Duplicate results with SQL join

SELECT `groups`.`name`, `groups`.`id`
FROM `groups`
JOIN `group_members`
    ON (`group_members`.`user_id` = `groups`.`user_id`)
WHERE `groups`.`user_id` = '33'
ORDER BY `groups`.`id`

I need to get g开发者_如何转开发roup's ID and group's name for every group he is member in. But results can't duplicate!

My query returns a lot of duplicate entries.

In groups are stored all groups that do exists (name, description etc.). In group_members are stored all users that are members of some group (user_id, group_id etc.).

How to modify this query to get not-duplicate, but correct results? Thanks in advice!

Edit:

In groups there are no user_id. All member of the group are stored in group_members table. I need to get all groups (group's ID and name) where user is member.

If you want to know all groups of one user, you have to join on the group_members.group_id and edit the WHERE clause.

SELECT `groups`.`name`, `groups`.`id`
FROM `groups`
JOIN `group_members`
    ON (`group_members`.`group_id` = `groups`.`id`)
WHERE `group_members`.`user_id` = '33'
ORDER BY `groups`.`id`

First, your query states:

`groups`.`user_id` = '33'

This seems incorrect because why would there be a user in the group-table?

Regarding the question. You are probably getting duplicates because a user can exist in more than one group. So, in other words if you only want exactly one group per user then it depends on which group you'd like to see. It could be the minimum group id, maximum or pretty much whatever you'd like.

You have confused some fields i guess, here you go:

SELECT `groups`.`name`, `groups`.`id` 
FROM `groups` 
JOIN `group_members`     
ON `group_members`.`group_id` = `groups`.`id` 
WHERE `groups_members`.`user_id` = '33' 
ORDER BY `groups`.`id

SELECT g.name, g.id
FROM groups g
where EXISTS (
  select 'x'
  from group_members gm
  where g.id = gm.group_id
    and gm.user_id = '33'
)
ORDER BY g.id