I have a very common query where i need to find the list of Users that have same or less than the brokers the user currently have
I want to find the colleague users which have same set of BROKERS or a subset of the user and not any Extra Broker
ID SiteUserID BrokerID
5009 19437 765
5008 19437 891
5419 26846 891
5421 26846 765
5117 40348 765
5914 40389 765
5915 40574 891
So for UserID 40574 – it return same 40574
For UserID 40389 – it return 40389 and 40348 as both have same broker
BUT for UserID 19437 – it will return 19437, 26846, 40348, 40349 and 40574 as the brokers in all the users are same set or subset
I am not able to achive this with simple In operator
Select SiteUserID from SiteUserBroker (nolock) where BrokerID in (
Select BrokerID from SiteUserBroker (nolock) where SiteUserID = @SiteUserID)
which obviously didn't worked as expected with any simple tweak.
I came up with
Select SUB.SiteUserID from SiteUserBroker SUB (nolock)
inner join (Select Count(*) as TotalBrokers , SiteUserID from SiteUserBroker group by
SiteUserID)Nested on Sub.SiteUserID = Nested.SiteUserID
where BrokerID in (
Select BrokerID from SiteUserBroker (nolock) where SiteUserID = @SiteUserID)
group by SUB.SiteUserID
Having Count(*) - MIN(TotalBrokers) >= 0
Is there a more simpler way!
any ideas are appreciated
Table create – to validate your results
create table #SiteInfo (ID int, SiteUserID INT, BrokerID int )
insert into #SiteInfo values (
5009, 19437, 765),
(5008, 19437, 891),
(5419, 26846, 891),
(5421, 26846, 765),
(5117, 40348, 765),
(5914, 40389, 765),
(5915, 40574, 891)
DECLARE @SiteUserId INT
SET @SiteUserId = '19437'
Best Answer
Look at