Duplicate results with SQL join

Question

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 group'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.

@cularis : it is not GROUP table representing the groups, it is GROUP_MEMBERS table representing connection between mamber and group... — Daniel Gruszczyk
– Daniel Gruszczyk, Commented Jul 22, 2011 at 9:22
The way I see it groups contains all groups and group_members has the members of all groups. If you want all groups a user is a member of, you have to join groups with group_members on id/group_id and then use where on group_members.user_id. — Jacob
– Jacob, Commented Jul 22, 2011 at 9:27
You said your query was returning duplicate rows, but then you said groups has no user_id on it. If that's the case, your query doesn't return duplicate rows, it fails, because it has WHERE groups.user_id = 33. Please post the actual table structure and the actual query you're having trouble with, otherwise you're just wasting everyone's time. — T.J. Crowder
– T.J. Crowder, Commented Jul 22, 2011 at 9:30

Jacob · Accepted Answer · 2011-07-22 09:18:55Z

5

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`

answered Jul 22, 2011 at 9:18

Jacob

43.6k6 gold badges81 silver badges81 bronze badges

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

2 Comments

ypercubeᵀᴹ Over a year ago

I think this is the solution. OP has joined on the wrong fields.

davorp Over a year ago

this works but it's good to use EXISTS to optimize: stackoverflow.com/questions/6787927/…

Andreas Ågren · Accepted Answer · 2011-07-22 09:21:45Z

1

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.

answered Jul 22, 2011 at 9:21

Andreas Ågren

3,94926 silver badges33 bronze badges

Comments

Daniel Gruszczyk · Accepted Answer · 2011-07-22 09:26:45Z

0

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

edited Jul 22, 2011 at 9:26

answered Jul 22, 2011 at 9:18

Daniel Gruszczyk

5,64210 gold badges53 silver badges89 bronze badges

1 Comment

Daniel Gruszczyk Over a year ago

I actually made some mistake because of a wrong assumption, he needs a join from groups to groups_members...

davorp · Accepted Answer · 2011-07-22 09:33:31Z

0

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

edited Jul 22, 2011 at 9:33

answered Jul 22, 2011 at 9:26

davorp

4,2663 gold badges29 silver badges34 bronze badges

Collectives™ on Stack Overflow

Duplicate results with SQL join

4 Answers 4

2 Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related