SQL Query without nested queries..

Question

Let's say we have these tables;

table user:
- id
- username
- email

table user2group:
- userid
- groupid

table group:
- id
- groupname

How do I make one query that returns all users, and the groups they belong to (as an array in the resultset or something..)

Answer 1

select u.id, u.username, u.email, g.groupid, g.groupname
from user u 
join user2group ug on u.userid=ug.userid
join group g on g.groupid=ug.groupid
order by u.userid

As you are looping through the result set, each time you see a new userid make a new user object (or whatever) and add the groups to it.

Answer 2

Eric's answer is great, but I would use a LEFT JOIN instead of an INNER to get users that do not belong to any group as well.

SELECT 
  u.id, 
  u.username, 
  u.email, 
  g.groupid, 
  g.groupname
FROM 
  user u 
  LEFT JOIN user2group ug ON u.userid = ug.userid
  LEFT JOIN group g ON g.groupid = ug.groupid
ORDER BY 
  u.userid

Answer 3

Both of the above are more or less correct (deepends if each user has a group or not). But they will also both give a result set with several entries for each user.

There are ways of concatenating every group member into one comma separated string, I'd suggest you read about it here: http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

Another method I personally like is to use bit values instead of the relational table user2group

table user then gets a int (or bigint) field group, and each group ID is assigned one bit value (ie: 1,2,4,8,16 and so on) The value of the user table's group field is then the sum of the groupID it's assigned to. To query if its got a group you do: where (group AND groupID = groupID)