I have 2 tables.

In the User Table I have a UserID and the CompanyID of the Company he
belongs to.

In the second table (EmailBlocked), are the users or company a particular
user has blocked.  If a user blocks a company, then all users in that
company are blocked.  If the company is not blocked, then only the
particular user in the record is blocked.

I can do this in 2 selects, but I am trying to get it to work in one.

DROP TABLE UserTable
go
CREATE TABLE UserTable
(
UserID int,
CompanyID varChar(15)
)
go
DROP TABLE EmailBlocked
go
CREATE TABLE EmailBlocked
( UserID int,
BlockedUserID int,
CompanyID int,
CompanyBlocked int
)
go
INSERT EmailBlocked (UserID,BlockedUserID,CompanyID,CompanyBlocked) VALUES
(309,150,5,1)
INSERT EmailBlocked (UserID,BlockedUserID,CompanyID,CompanyBlocked) VALUES
(168,130,Null,0)
INSERT EmailBlocked (UserID,BlockedUserID,CompanyID,CompanyBlocked) VALUES
(171,120,250,0)
INSERT UserTable (UserID,CompanyID) VALUES (152,4)
INSERT UserTable (UserID,CompanyID) VALUES (150,5)
go

This Select is close, but not quite right.

There are 2 tests;
    1) Check if the 2 users match (UserID and BlockedUserID are in a record
in the table).

    2) Check to see if the User (UserID) has the company blocked that the
2nd user (BlockedUserID) belongs to.

Pass back the BlockedUserID and null for the companyID #1 and Pass back Null
for the BlockedUserID and the CompanyID for #2.  If neither (nothing is
blocked for these users), I should get no record back.

In the following, I am getting no record back.  Test #1, there is no match.
Test #2, 309 has blocked Company 5, and 150 is part of company 5 so he
should have been blocked and I would like to get the record back as
"NULL,5".  This tells me that the company is what is being blocked.

Declare @UserID bigint,@BlockedUserID bigInt

Select @UserID = 309,@BlockedUserID = 152
Select BlockedUserID,CompanyID
from EmailBlocked
where (UserID = @UserID and
BlockedUserID = @BlockedUserID) or
(CompanyID in (select CompanyID
  from UserTable
  where UserID = @BlockedUserID) and CompanyBlocked = 1)

Thanks,

Tom

Hi, Tom

I think this query gives the expected results:

select
    case when CompanyID is null
        then BlockedUserID
    end as BlockedUserID,
    CompanyID
from (
    select (
        Select BlockedUserID
        from EmailBlocked
        where UserID = @UserID
        and BlockedUserID = @BlockedUserID
    ) as BlockedUserID,
    (
        select CompanyID
        from EmailBlocked
        where UserID = @UserID and CompanyBlocked = 1
        and CompanyID in (
            select CompanyID
            from UserTable
            where UserID = @BlockedUserID
        )
    ) as CompanyID
) x where BlockedUserID is not null or CompanyID is not null

However:
1. It's pretty complicated; I would rather use two queries.
2. The DDL is somewhat strange:
a) the data type of CompanyID should be the same in both tables
b) you have no primary keys, foreign keys, check constraints, etc. You
should have (at least) primary keys for each table, foreign keys where
appropriate, and "not null" columns where a value is required. Ideally,
you should have check constraints (and/or other verifications) so you
cannot insert any inconsistent data in the database. For example, you
should have a check constraint that says: "CompanyBlocked=0 OR
CompanyID is not null", so you cannot block an unspecified company.
Also, a primary key in the UserTable, would make it clear if a user
belongs to only one company or if it is allowed that a user belong to
more than one company.
c) to block a company is it really necessary to specify a blocked user
(within that company) ? If no, a different DDL would be more useful. If
yes, a constraint should be added so to make sure that the blocked user
is really in the blocked company. This may be implemented using a
foreign key on two columns referencing the UserTable.
d) when you block only a user (i.e. CompanyBlocked=0), what is the
purpose of specifying the company where he works ? If there is no
purpose, a different DDL would be more useful, again.

Razvan

Hi Razvan,

Razvan,

This seems to do exactly what I needed.

I thought I would need to use a derived table somehow, but couldn't figure
out how to make it work.

I am trying to figure out the thought process that leads to building the
first table and then building the table around that.

below:

Show quoteHide quoteWhy would you use 2 queries if one query works, as yours seems to?

You're right.  This was a mistake.  The companyID is supposed to be an Int.
In my actual table, it is created as an Identity.

I agree.  But this is just a quick DDL to allow you (and others) to see what
I am trying to do.  It isn't exactly the same as my normal tables (which do
have primary keys, foreign keys and constraints).  My UserTable does use
UserID as the Primary key.  The EmailBlocked Table does have a Primary key
(EmailBlockedID Int Identity).  My Foreign key would be UserID referencing
UserID in the UserTable.

True, but I am enforcing this in my Code.

And it is.  It is an identity as I mentioned earlier.  But I may change this
to some random number so it cannot be guessed.

No.  Actually, the UserID is irrelavant if CompanyBlocked = 1.  Actually, in
my code, if I have 3 users defined from the same company (none have
CompanyBlocked = 1) and I later Block the company - I delete 2 of the
records and set the CompanyBlocked=1 on the record I choose to keep.  This
way there is only one record with the CompanyBlocked set for any User.

It isn't.  And the CompanyID is irrelavant if the CompanyID = 0.

As a matter a fact, if it were necessary to check the CompanyBlocked field
first to see if a company blocked before doing the UserID/BlockedID test, I
assume this would need to be done using 2 separate queries.

Thanks,

Tom
Show quoteHide quote

CHARINDEX in CASE Within SELECT Statement
Default Value Calculation
Deadlock updating different rows
No retrieving dupicate rows
Service Broker - BEGIN DIALOG CONVERSATION @dialog_handle