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?

Answer 1

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.