SQL query without nested queries

Question

Say we have these tables;

table user:
- id
- username
- email

user2group table:
- userid
- groupid

table group:
- id
- groupname

How to make a single request that returns all users and groups to which they belong (as an array in the result set or something like that).

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

When you iterate over the result set, every time you see that the new userid creates a new user object (or something else) and adds groups to it.

Answer 2

Eric answer is great, LEFT JOIN INNER, .

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

(, ). .

, : http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

, , user2group

receives a group of int (or bigint) fields, and each bit group identifier is assigned one bit value (i.e.: 1,2,4,8,16, etc.). The value of the field of the user table group is equal to and then the sum of its groupID. To ask if you have a group that you do: where (group AND groupID = groupID)