CODEWITHSUNDEEP
sqlsql-servert-sql
Ryan
Ryan

Reputation: 635

TSQL join and intersection

I've got a table called GroupMember.

GroupName    MemberId
----------------------
 A            101
 A            102
 B            106
 C            110
 C            101

I'm trying to write a stored procedure to retrieve members of more than one group. The application user can select number of GroupNames and the operator ( AND - OR ).

for OR operator I simply pass the GroupNames to stored procedure and Join the two:

Create procedure JoinIntersect(@GroupNames userDefinedTableType1 readonly)

as
  select distinct gm.MemberId 
    from GroupMember gm 
    inner join @GroupNames selectedGroups
    on (gm.GroupName = selectedGroups.Name)

in this case if I pass A, C as selected groups the result would be:

MemberId
--------
101
102
110

How can I implement the AND operator? Users who are a member of A AND C?

Upvotes: 1

Views: 65

Answers (1)

lc.
lc.

Reputation: 116458

You can re-use the same query but add a HAVING clause:

select gm.MemberId 
from GroupMember gm 
inner join @GroupNames selectedGroups on (gm.GroupName = selectedGroups.Name)
group by gm.MemberId
having count(*) = (select count(distinct Name) from @GroupNames)

SQL Fiddle demo

In layman's terms, this checks to see if the number of groups the member belongs to is the same as the number of groups requested. That is, if you request members in both groups A and C, it will select all members of A and C then pick out the ones with two (i.e. both) memberships.

Upvotes: 2

Related Questions