MySQL: How to exclude duplicates with a join?

Question

I am trying to select all of the users who belong to a group. My group_members table keeps track of which groups a user belongs to.

group_members ( group_id , user_id )

So in my group_members table, a user_id may appear multiple times because a user can belong to many groups. I'm using an innerJoin() to pull the user information for each user_id. However, I only want to display a user's name once when I retrieve it.

How can I do this?

ChssPly76 · Accepted Answer · 2009-12-03 02:01:55Z

3

Use DISTINCT. Either:

select distinct u.* from users u
  join group_members gm on gm.user_id = u.user_id
 where ...

or

select * from users u
 where u.user_id in (select user_id from group_members where ...)
   ...

Run "EXPLAIN SELECT" on both queries to see which one is faster in your case.

answered Dec 3, 2009 at 2:01

ChssPly76

101k25 gold badges210 silver badges196 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL: How to exclude duplicates with a join?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related