Comparing results

I've got a table (UsersOrgs) with two fields, OrgID and UserID. Both type int.

I've got two sql statements:
select OrgID From UsersOrgs Where UserID = @User1
select OrgID From UsersOrgs Where UserID = @User2

Those return a list of OrgID's for each user.

I need a boolean sql statement that will determine if @User1 has at least one OrgID in common with @User2.

ie.
@User1's OrgID's: 7,8
@User2's OrgID's: 1,7,9
result: true
forloopAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

forloopAuthor Commented:
Edited text of question.
0
chigrikCommented:
Try this (it's my test example):

CREATE TABLE UsersOrgs (
        UserID int NOT NULL ,
        OrgID  int NOT NULL
)
GO

ALTER TABLE UsersOrgs WITH NOCHECK ADD
        CONSTRAINT PK_UsersOrgs PRIMARY KEY  NONCLUSTERED
        (
                UserID,
                OrgID
        )
GO

insert into UsersOrgs (UserID, OrgID) VALUES (1, 7)
insert into UsersOrgs (UserID, OrgID) VALUES (1, 8)
insert into UsersOrgs (UserID, OrgID) VALUES (2, 1)
insert into UsersOrgs (UserID, OrgID) VALUES (2, 7)
insert into UsersOrgs (UserID, OrgID) VALUES (2, 9)
GO

DECLARE @User1 int
DECLARE @User2 int
select @User1 = 1
select @User2 = 2

select flag =
CASE
  WHEN (select OrgID From UsersOrgs Where UserID = @User1 and OrgID in
       (select OrgID From UsersOrgs Where UserID = @User2)) is not NULL
    then 'True'
  ELSE 'False'
END
GO

delete from UsersOrgs
GO

insert into UsersOrgs (UserID, OrgID) VALUES (1, 7)
insert into UsersOrgs (UserID, OrgID) VALUES (1, 8)
insert into UsersOrgs (UserID, OrgID) VALUES (2, 1)
insert into UsersOrgs (UserID, OrgID) VALUES (2, 9)
GO

DECLARE @User1 int
DECLARE @User2 int
select @User1 = 1
select @User2 = 2

select flag =
CASE
  WHEN (select OrgID From UsersOrgs Where UserID = @User1 and OrgID in
       (select OrgID From UsersOrgs Where UserID = @User2)) is not NULL
    then 'True'
  ELSE 'False'
END
GO
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.