Removing Multiplicity in INNER JOIN Query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Removing Multiplicity in INNER JOIN Query

Author

Topic

jimmyb1651
Starting Member

5 Posts

Posted - 2002-04-03 : 11:18:49

I am creating a query that INNER JOINs two tables with a many-to-many relationship. I have one table for Users, and one for Groups. In a search feature I can search for Users who are in Group 1 AND Group 2.

SELECT
   MyUsers.MyName
FROM
   MyUsers INNER JOIN UsersGroups ON UsersGroups.MyUserID = MyUsers.MyUserID
WHERE
   UsersGroups.MyGroupID = 1
   OR
   UsersGroups.MyGroupID = 2
ORDER BY
   MyUsers.MyName ASC;

This query returns multiple rows for a User if they belong to both groups. I only want to return each user once.

Can anyone tell me how to do this?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-04-03 : 11:29:57

Use SELECT DISTINCT:

SELECT DISTINCT
MyUsers.MyName
FROM
MyUsers INNER JOIN UsersGroups ON UsersGroups.MyUserID = MyUsers.MyUserID
WHERE
UsersGroups.MyGroupID = 1
OR
UsersGroups.MyGroupID = 2
ORDER BY
MyUsers.MyName ASC;

DISTINCT removes duplicate values from the result set.