mysql - SQL JOIN 在单列中返回两个表

Question

如何执行 SQL JOIN 并在单个列中返回两个表？

例如，对于给定的组 ID，我如何获得属于该组的所有人及其所有 friend 的单个列表？

团体

-id
-name

groups_have_people

-groups_id
-people_id

人

-id
-name
-group_id

people_have_friends

-people_id1 (references people.id)
-people_id2 (references people.id)

例如，groups_have_people 包括组 123 的 people_id 1、2 和 3，people_have_friends 包括记录 1-2 , 1-3, 1-4, 2-1, 2-5, 我应该得到以下结果:

id Name
1  Mary
2  John
3  Burt
4  Jill
5  Sue

我可以做类似下面的事情，但它不会返回一个列表。

SELECT p1.*, p2.*
FROM people p1
INNER groups_have_people ghp ON ghp.people_id=p1.id
LEFT OUTER JOIN people_have_friends phf ON p1.id=phf.people_id1
LEFT OUTER JOIN people p2 ON p2.id=phf.people_id2
WHERE ghp.groups_id=123;

编辑 看起来我可能不使用 JOIN 来这样做，应该做类似下面的事情吗？

SELECT p.*
FROM people p
INNER groups_have_people ghp ON ghp.people_id=p.id
WHERE ghp.groups_id=123
UNION
SELECT people p1
INNER JOIN people_have_friends phf ON p1.id=phf.people_id1
INNER JOIN people p2 ON p2.id=phf.people_id2
WHERE p2.groups_id=123;

编辑 2. 添加更多样本数据

团体

id  name
111 Green
123 Blue
222 Yello

人

id Name
1  Mary
2  John
3  Burt
4  Jill
5  Sue
6  Bob
7  Wilma

groups_have_people

groups_id  people_id
111        1
111        5
123        1
123        2
123        3
222        3
222        7

people_have_friends

people_id1  people_id2
1           2
1           3
1           4
2           1
2           5
4           5
5           6

编辑 3. 像下面这样的东西怎么样？

SELECT DISTINCT id,
                name
FROM people
WHERE id IN
    ( SELECT people_id
     FROM groups_have_people
     WHERE groups_id=123
     UNION SELECT phf.people_id
     FROM people_have_friends phf
     INNER JOIN groups_have_people ghp ON ghp.people_id=phf.people_id
     WHERE ghp.groups_id=123);

或

SELECT DISTINCT id,
                name
FROM people
INNER JOIN
  (SELECT people_id
   FROM groups_have_people
   WHERE groups_id=123
   UNION SELECT phf.people_id
   FROM people_have_friends phf
   INNER JOIN groups_have_people ghp ON ghp.people_id=phf.people_id
   WHERE ghp.groups_id=123) x ON x.people_id=people.id;

Answer 1

http://sqlfiddle.com/#!9/89a12/3

SELECT 
  p.id,
  p.name
FROM groups_have_people ghp
INNER JOIN people p 
ON ghp.people_id=p.id
WHERE ghp.groups_id=123
UNION
SELECT 
  p.id,
  p.name
FROM groups_have_people ghp
INNER JOIN people p_temp 
ON ghp.people_id=p_temp.id
INNER JOIN people_have_friends phf 
ON p_temp.id=phf.people_id1
INNER JOIN people p 
ON phf.people_id2=p.id
WHERE ghp.groups_id=123