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

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().

Simple join:

Code:

---

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

---

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.

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

Quote:

---

Originally Posted by jmcilhinney

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.

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

Code:

---

        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
                )

---