[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 226
  • Last Modified:

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
0
forloop
Asked:
forloop
1 Solution
 
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

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now