[RESOLVED] Many to Many problem: List the users that belong to all groups

[jcis]

I just don't remember how to do it:

3 Tables:

User: @UserCode, Name
Group: @GroupCode, GroupName
UserGroup: @UserCode, @GroupCode (Associative Table)

- How to list all the Users that belong to all groups?

I just don't remember the correct way to do it, i don't want to do it using Count().

[jmcilhinney]

Simple join:

SELECT User.UserCode, User.Name, [Group].GroupCode, [Group].GroupName
FROM User
INNER JOIN UserGroup
ON User.UserCode = UserGroup.UserCode
INNER JOIN [Group]
ON [Group].GroupCode = UserGroup.GroupCode

[jcis]

But what i need is the list of Users that are assigned to all groups, excluding those Users that are assigned to some Groups but not all.

Example:

UserGroup Table:
Name GroupCode
-------- ---------
Juan 1
Juan 3
Pepe 1
Pepe 2
Sonia 1
Sonia 2
Sonia 3
Sonia 4

Group Table:
Code GroupName
---- ------------------
1 Group1
2 Group2
3 Group3
4 Group4

In this Case the Query should only return Sonia, because she is the only user assigned to all groups.

[jmcilhinney]

Oh, I see. I thought it seemed a bit too easy. I'll cogitate on it a bit.

[jcis]

Oh, I see. I thought it seemed a bit too easy. I'll cogitate on it a bit.

no prob , i remember this is an especial case that requieres certain trick i just don't remember, this case is normally used in tests. I remember it was something about getting those users that have some groups assigned but not all, and then doing a NOT IN on the rest of the users, i just can't remember how it was.

[jcis]

I just found a way to do it, i don't know if it's the best way but it works..

	SELECT User.UserCode, User.Name
          FROM User
	 WHERE NOT EXISTS
		(
			SELECT 1
		          FROM Group LEFT JOIN UserGroups 
                            ON Group.GroupCode = UserGroup.GroupCode 
                           AND UserGroup.Name= User.Name
			 WHERE UserGroup.Name IS NULL
		)