Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

up vote 3 down vote accepted

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 |
share|improve this answer
    
Thanks, it was the UNION ALL query I was looking for. –  xylar Jul 2 '13 at 7:00

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 !!!

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.