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'
  • find the list of Users that have same or less than the brokers the user currently have What if user A have brokers 1,2,3, and user B have brokers 1,4? Does user B must be returned for selected user A (they have the same broker 1, and user B have 2 brokers whereas user A have 3 brokers)? Or returned user must do NOT have another brokers? – Akina Dec 20 '18 at 9:00
  • @Akina B must not be returned. I need to find the users who are a subset. Any extra will make them ineligible - thanks – Moons Dec 20 '18 at 9:47

Look at

FROM SiteUserBroker


SELECT t1.SiteUserID
FROM SiteUserBroker t1
LEFT JOIN SiteUserBroker t2 ON  t1.BrokerID = t2.BrokerID
                            AND t2.SiteUserID = @SiteUserID
  • Seems to be the same performance on the query either with left join as with not exists. My bad – Randi Vertongen Dec 20 '18 at 9:45
  • @Moons You need in optimization, not in building correct query text, is it? How do you think, can it be performed on the simplified table model and tiny data array? – Akina Dec 20 '18 at 10:04
  • @Moons not correct - returning any results. Using my query (after editing tablename) on your model I see the result which is equal to one you show in a question... what input parameter gives incorrect result? – Akina Dec 20 '18 at 10:10
  • Look at a fiddle. And show where is incorrectness. Please... – Akina Dec 20 '18 at 10:14
  • @Akina Apologies Apologies - i messed up a lot. thank you for your help. I will compare it with huge data of my production system. This was suppose to be a part of big query. извиняюсь – Moons Dec 20 '18 at 10:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.