mysql - 选择一个单元格一次，但其他所有内容

Question

我有两个表:

      groups                  group_members
    v-------------------------------v
 +----+------------+     +----+----------+------+
 | id | group_name |     | id | group_id | name |
 +----+------------+     +----+----------+------+
 | 1  | admin      |     | 1  | 1        | Dave |
 +----+------------+     +----+----------+------+
 | 2  | moderator  |     | 2  | 1        | Bob  |
 +----+------------+     +----+----------+------+
 | 3  | vip        |     | 3  | 2        | Sue  |
 +----+------------+     +----+----------+------+
 | 4  | user       |     | 4  | 2        | Jill |
 +----+------------+     +----+----------+------+
                         | 5  | 2        | Jack |
                         +----+----------+------+
                         | 6  | 3        | Bill |
                         +----+----------+------+
                         | 7  | 4        | Sam  |
                         +----+----------+------+

group_members 具有从 group_id 到 groups 中的 id 的外键引用。

我想获取在一个群组中找到的成员花名册，同时返回过程中的group_name。我只想在一个查询中执行此操作。

我试过这个查询，但它返回每一行的组名称，我希望将 group_name 视为它自己的行。

SELECT groups.group_name, 
       group_members.name 
FROM   groups, 
       group_members 
WHERE  group_members.group_id = groups.id 
       AND groups.id = 1 

结果是:

+-------------------+--------------------+
| groups.group_name | group_members.name |
+-------------------+--------------------+
| admin             | Dave               |
+-------------------+--------------------+
| admin             | Bob                |
+-------------------+--------------------+

我想看看:

  either this |                     or this |
              v                             v
+-------------------+            +---------------------+
| groups.group_name |            | groups_members.name |
+-------------------+            +---------------------+
| admin             |            | admin               |
+-------------------+            +---------------------+
                                 | Dave                |
+---------------------+          +---------------------+
| groups_members.name |          | Bob                 |
+---------------------+          +---------------------+
| Dave                |
+---------------------+
| Bob                 |
+---------------------+

这可能吗？

Answer 1

SELECT if(gm.name='',g.name,gm.name) as name
FROM   groups g
       join (select group_id, name from group_members gm
                  union all
                  select 1 as group_id, '' as name) gm on
       gm.group_id = g.id 
WHERE
       groups.id = 1 

我们可以再添加一个人工行并选择群组名称(对于额外的行)或群组成员名称