I'm sorry, I don't know how to ask this question without giving you the whole story:
Consider the following tables:
**users**
ID | name
1 | Shawn
2 | John
3 | Josh
**groups**
groupName | userID
groupA | 1
groupA | 2
groupB | 1
This means that Shawn and John are part of group A, and Shawn is part of group B. Josh is part of no group at all.
What I want to do is to list every user and see to which group he belongs. Here is what I would try:
SELECT name, groupName FROM users, groups WHERE userID = ID GROUP BY name
Which outputs
name | groupName
Shawn | groupA
John | groupA
Here is my problem
I can't see that Shawn is part of group B as well. Nor can I see Josh at all.
my question
How can I get something like this instead?
name | groupName
Shawn | groupA, groupB
John | groupA
Josh | none
Or at least something like this:
Shawn | groupA
Shawn | groupB
John | groupA
Josh |
I was thinking along theses lines:
SELECT name, groupName FROM users, groups WHERE (userID = ID GROUP BY name OR 1)
but I can't find the solution
Thanks you all in advance