CODEWITHSUNDEEP
sql
Mike
Mike

Reputation: 559

Link Table - How to get a list of items in list

I have a link table called AccountGroupLinks. It has two columns:

For each of the groups assigned to account X, get a distinct list of accounts assigned to each group excluding group Y. I can't seem to figure this SQL statement out. Any help would be greatly appreciated.

Upvotes: 0

Views: 28

Answers (2)

Mike
Mike

Reputation: 559

Thank you @sstan. I was trying to do nested select statements. This got me on the right direction. I had to make a small tweak to get it to work:

select distinct t2.GroupId, t2.AccountId
  from AccountGroupLinks t1
  join AccountGroupLinks t2
    on t2.GroupId = t1.GroupId
   and t2.GroupId <> 'Y'
   and t2.accountId <> 'X'
 where t1.AccountId = 'X'

Upvotes: 0

sstan
sstan

Reputation: 36513

You'll need a self join to do this:

select distinct t2.GroupId, t2.AccountId
  from AccountGroupLinks t1
  join AccountGroupLinks t2
    on t2.GroupId = t1.GroupId
   and t2.GroupId <> 'Y'
 where t1.AccountId = 'X'

Upvotes: 1

Related Questions