Consolidation problem

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,CompanyB
locked) VALUES
(309,150,5,1)
INSERT EmailBlocked (UserID,BlockedUserID,CompanyID,CompanyB
locked) VALUES
(168,130,Null,0)
INSERT EmailBlocked (UserID,BlockedUserID,CompanyID,CompanyB
locked) 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,
TomHi, 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:
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1135236075.969255.167240@.o13g2000cwo.googlegroups.com...
> 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.
Why would you use 2 queries if one query works, as yours seems to?

> 2. The DDL is somewhat strange:
> a) the data type of CompanyID should be the same in both tables
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.

> 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.
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.

>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.
True, but I am enforcing this in my Code.

> 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.
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.

> c) to block a company is it really necessary to specify a blocked user
> (within that company) ?
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.

> 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.
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
> Razvan
>