Hi!
I'm trying to write a query involving the INTERSECT operator that goes like this:
SELECT UserId, UserName FROM GroupMemberShip JOIN User ON GroupMembership.UserId = User.UserId WHERE GroupId = 1
INTERSECT
SELECT UserId, UserName FROM GroupMemberShip JOIN User ON GroupMembership.UserId = User.UserId WHERE GroupId = 4
...
What I'm trying to retrieve is the list of users who belong to both Group 1, Group 4 and possibly more (there's a variable number of groups, not just 2). I haven't tried set operations with LLBL Gen Pro before.
If set operations aren't supported, perhaps I could re-write the query to something like:
SELECT UserId FROM GroupMembership WHERE GroupId IN (1, 4, ...)
GROUP BY UserId HAVING COUNT(1) = n;
Replace 'n' in the "COUNT(1) = n" expression with the number of groups you are searching for.
I haven't really spent much time with group functions in LLBLGen so, for me, this seems a little complex to do (although this SQL would be run more efficiently due to the lack of set operations).
Any help appreciated.
Regards,
Nitin
