加入一个表,两个独立的一个一对多表
问题描述:
我有三个表,就像这样:加入一个表,两个独立的一个一对多表
projects
| id | name |
|----|------------|
| 1 | enterprise |
| 2 | discovery |
widgets
| project_id | name |
|------------|-----------------|
| 1 | saucer section |
| 1 | pylons |
| 2 | spinning saucer |
| 2 | angular pylons |
sprockets
| project_id | name |
|------------|-------------|
| 1 | engineering |
| 1 | bridge |
| 1 | mess |
| 2 | engineering |
| 2 | bridge |
| 2 | mess |
我想写的是,给我正好十个结果的查询:基本上是一个为每行widgets
和sprockets
,看起来像这样:
result
| project_name | widget_name | sprocket_name |
|--------------|-----------------|---------------|
| enterprise | saucer section | null |
| enterprise | pylons | null |
| enterprise | null | engineering |
| enterprise | null | bridge |
| enterprise | null | mess |
| discovery | spinning saucer | null |
| discovery | angular pylons | null |
| discovery | null | engineering |
| discovery | null | bridge |
| discovery | null | mess |
相反,我的连接被合并到返回12行。增加一个组似乎减少了太多。
我已经试过类似以下,但连接被乘以:
select
p.name as project_name,
w.name as widget_name,
s.name as sprocket_name
from
projects as p
left join widgets w on p.id = w.project_id
left join sprockets s on p.id = s.project_id;
的answersI'vefound主要集中在重新设计的数据库,但是这不是我的选择。 如何编写连接以从此数据集返回上述十行?
答
使用与每个表连接的UNION
。
SELECT p.name AS project_name, w.name AS widget_name, NULL AS sprocket_name
FROM projects AS p
INNER JOIN widgets AS w ON p.id = w.project_id
UNION ALL
SELECT p.name AS project_name, NULL AS widget_name, s.name AS sprocket_name
FROM projects AS p
INNER JOIN sprockets AS s ON p.id = s.project_id
这样做的结果是20行,其中一些数据看起来与我想要的不相关。我错过了什么吗? –
糟糕,忘记了'ON'子句 – Barmar
如果您将“p”添加到“FROM projects AS p”的第二行,我会接受这个答案:) –