SQL外部连接一张桌子，带2个表

Question

我需要一些SQL帮助。我是一个行业的Java家伙，我真的不知道如何问这个问题。我有3张桌子，打电话给他们，孩子，朋友。人是ID和名称：

|   id   |    name    |
---------------------
|    1   |    Joe     |

说孩子是一样的，但fk回到人

|   id   |    personId    |   name   |
-------------------------------------
|   1    |       1        |   Frank  |
|   2    |       1        |   Dan    |

和朋友是同一回事

|   id   |    personId    |   name   |
-------------------------------------
|   1    |       1        |   Will   |
|   2    |       1        |   Bob    |

显然，这是我真正问题的简化版本，但是结构是相同的。我需要用一个SQL拉出所有这些数据，以便我回到此数据

 | personId  | personName  |  childId  | childName |  friendId  |  friendName
------------------------------------------------------------------------------------------
 |     1     |    Joe      |     1     |   Frank   |   null     |    null
 |     1     |    Joe      |     1     |   Dan     |   null     |    null
 |     1     |    Joe      |    null   |   null    |     1      |    Will
 |     1     |    Joe      |    null   |   null    |     2      |    Bob

我尝试过多种加入技术，但似乎无法破解它。SQL从来都不是我最好的主题。现在，我将其解析为一个有朋友和孩子的列表的Java对象的人，这显然也可以使用：

 | personId  | personName  |  childId  | childName |  friendId  |  friendName
------------------------------------------------------------------------------------------
 |     1     |    Joe      |    null   |   null    |   null     |    null
 |   null    |    null     |     1     |   Frank   |   null     |    null
 |   null    |    null     |     1     |   Dan     |   null     |    null
 |   null    |    null     |    null   |   null    |     1      |    Will
 |   null    |    null     |    null   |   null    |     2      |    Bob

任何允许我的代码中循环清洁循环的任何东西。

谢谢！

Answer 1

select 
     p.id    as personId
  ,  p.name  as personName
  ,  c.id    as childId
  ,  c.name  as childName
  ,  null    as friendId
  ,  null    as friendName
from person p
  inner join child c
    on p.id = c.personId
union all 
select 
     p.id    as personId
  ,  p.name  as personName
  ,  null    as childId
  ,  null    as childName
  ,  f.id    as friendId
  ,  f.name  as friendName
from person p
  inner join friend f
    on p.id = f.personId;

rextester：http：//rextester.com/bspec33394

返回：

+----------+------------+---------+-----------+----------+------------+
| personId | personName | childId | childName | friendId | friendName |
+----------+------------+---------+-----------+----------+------------+
|        1 | joe        | 1       | frank     | NULL     | NULL       |
|        1 | joe        | 2       | dan       | NULL     | NULL       |
|        1 | joe        | NULL    | NULL      | 1        | will       |
|        1 | joe        | NULL    | NULL      | 2        | bob        |
+----------+------------+---------+-----------+----------+------------+

Answer 2

您可以与儿童和朋友的结合进行外部连接，然后检查您要与哪一个匹配以确定每列中输出的内容（使用case when）：

select    person.id,
          person.name,
          case when rel.kind = 1 then rel.id   end as childId,
          case when rel.kind = 1 then rel.name end as childName,
          case when rel.kind = 2 then rel.id   end as friendId,
          case when rel.kind = 2 then rel.name end as friendName
from      person
left join (
           select id, personId, name, 1 as kind
           from   children
           union all
           select id, personId, name, 2 as kind
           from   friends
          ) as rel
       on rel.personId = person.id
order by  person.id,
          rel.kind
          rel.id

Answer 3

您可以使用左JOIN来实现以下操作：

SELECT 
     p.id    as personId
  ,  p.name  as personName
  ,  c.id    as childId
  ,  c.name  as childName
  ,  f.id    as friendId
  ,  f.name    as friendName  
FROM person p
LEFT OUTER JOIN child c ON p.id = c.personId
LEFT OUTER JOIN friend f ON p.id = f.personId;

左JON的更多信息您可以在此处阅读：https://www.w3schools.com/sql/sql_join_left.asp