Grouping results based on link table

I have three tables:

users

+----+-------+
| id | name  | 
+----+-------+
| 1  | dave  |
| 2  | cher  |
| 3  | meg   |
| 4  | sarah |
+----+-------+

groups

+----+-------+
| id | name  |
+----+-------+
| 1  | admin |
| 2  | super |
+----+-------+

user_groups

+----+---------+----------+
| id | user_id | group_id |
+----+---------+----------+
| 1  | 1       | 1        |
| 1  | 2       | 1        |
+----+---------+----------+

I want to create a query so that I return an array of group names which contains an array of users. I don’t want to return groups which have no users, but I would like to return users which have no group.

admin
    - dave
    - cher
- meg
- sarah

At the moment I am getting the groups which have items first.

SELECT name FROM groups
INNER JOIN user_groups ON groups.id = user_groups.group_id

Then iterating through the groups and adding users.

SELECT id, name FROM users
INNER JOIN user_groups ON users.id = user_groups.user_id
WHERE user_groups.group_id = $group_id

Is there a way to combine these queries?

You should be able to easily combine your current queries to get the group and users by using the following:

SELECT g.name group_name, u.id, u.name
FROM groups g
INNER JOIN user_groups ug
  ON g.id = ug.group_id
INNER JOIN users u
  on ug.user_id  = u.id;

See SQL Fiddle with Demo. This will give you multiple rows for each group if there is more than one user per group, similar to this:

| GROUP_NAME | ID | NAME |
--------------------------
|      admin |  1 | dave |
|      admin |  2 | cher |

You could also use the GROUP_CONCAT() function to return a comma separated list of the users with each group:

SELECT g.name group_name, 
  GROUP_CONCAT(u.name SEPARATOR ', ') name
FROM groups g
INNER JOIN user_groups ug
  ON g.id = ug.group_id
INNER JOIN users u
  on ug.user_id  = u.id
GROUP BY g.name;

See SQL Fiddle with Demo. This returns a result:

| GROUP_NAME |       NAME |
---------------------------
|      admin | cher, dave |

Then if you want to also include the users that have no group associated with them, you could use a UNION ALL query:

SELECT g.name group_name, u.id, u.name
FROM groups g
INNER JOIN user_groups ug
  ON g.id = ug.group_id
INNER JOIN users u
  on ug.user_id  = u.id
UNION ALL
SELECT '' group_name, u.id, u.name
FROM users u
WHERE id not in (select user_id
                 from user_groups);

See SQL Fiddle with Demo. This returns:

| GROUP_NAME | ID |  NAME |
---------------------------
|      admin |  1 |  dave |
|      admin |  2 |  cher |
|            |  3 |   meg |
|            |  4 | sarah |

Perhaps something like this:

SET @given_group = 1;
SELECT
    IFNULL(`group`,'-') `group`,
    user
FROM
(SELECT A.name `group`,B.name user,0 groupwithusers
FROM user_groups AB
INNER JOIN groups A ON AB.group_id = A.id
INNER JOIN users B ON AB.user_id = B.id
WHERE AB.group_id = @given_group
UNION
SELECT NULL,A.name,1
FROM users A LEFT JOIN user_groups B
ON A.id = B.user_id
WHERE B.id IS NULL) M
ORDER BY groupwithusers;

When I run it, I get this:

mysql> SET @given_group = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT
    ->     IFNULL(`group`,'-') `group`,
    ->     user
    -> FROM
    -> (SELECT A.name `group`,B.name user,0 groupwithusers
    -> FROM user_groups AB
    -> INNER JOIN groups A ON AB.group_id = A.id
    -> INNER JOIN users B ON AB.user_id = B.id
    -> WHERE AB.group_id = @given_group
    -> UNION
    -> SELECT NULL,A.name,1
    -> FROM users A LEFT JOIN user_groups B
    -> ON A.id = B.user_id
    -> WHERE B.id IS NULL) M
    -> ORDER BY groupwithusers;
+-------+-------+
| group | user  |
+-------+-------+
| admin | dave  |
| admin | cher  |
| -     | meg   |
| -     | sarah |
+-------+-------+
4 rows in set (0.00 sec)

mysql>

Give it a Try !!!