mysql relationship

[slick101]

Hey guys so i have 2 tables.

Groups table & mods table

Groups:
group_id, user_id, group_name

Mods:
group_id, user_id

I am trying write a query that will return me all groups related to a user. But some groups are related to a user if they are present in mods table.

so lets say:
Groups:
1 | 5 | group1
2 | 3 | group2

Mods:
1 | 3

So this would be a left join thing?

SELECT g.* FROM groups g
LEFT JOIN mods m ON m.user_id = g.user_id
WHERE g.user_id = 2

Only returns me group 2 wwhere i would like the result to return me group 1 & 2 since user_id : 3 is a mod of group 1

Any help would be great thanks!

[Bjom]

Your query should not return any result, since there is nowhere a user_id=2.

That being said and assuming you want to use user_id = 3 actually:

1. Join on group_id, since you are actually preparing info about the groups and not about users (info that gets filtered per user later...).
2. You need to add to your WHERE condition like so:

SELECT g.group_name FROM groups AS g
LEFT JOIN mods AS m ON m.group_id = g.group_id
WHERE g.user_id = 3 OR m.user_id = 3

unless i missed something that should do the trick

Bjom