Sql Query with In Operator optmization 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) &gt;= 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'
Look at

SELECT DISTINCT SiteUserID  
FROM SiteUserBroker

EXCEPT 

SELECT t1.SiteUserID
FROM SiteUserBroker t1
LEFT JOIN SiteUserBroker t2 ON  t1.BrokerID = t2.BrokerID
                            AND t2.SiteUserID = @SiteUserID
WHERE t2.SiteUserID IS NULL