sql - 将两个 JOIN 查询组合在一起

Question

我有 3 个表 Appliance、Tx_property、Rx_property

#Appliance Table structure:
  -id
  -tx_property_id
  -rx_property_id
  -...

#Tx_property Table structure:
  -id
  -...

#Rx_property Table structure:
  -id
  -...

在 rx_property.id = appliance.id 和 tx_property.id = appliance.id 上设置约束

是否可以只运行一个查询来检索 Appliance 表中具有相应条目的所有记录(基于 rx_property_id 和 tx_property_id)？

总而言之，我想将下面的 2 个查询合并为一个。

select * from appliance INNER JOIN rx_property ON rx_property.id= appliance.rx_property_id;
select * from appliance INNER JOIN tx_property.id ON tx_property.id= appliance.tx_property_id;

有什么想法吗？谢谢!

编辑:

所以如果我有如下示例记录:

appliance records:
id = 70
tx_property_id = 11
rx_property_id = null

id = 71
tx_property_id = 12
rx_property_id = null

id = 72
tx_property_id = null
rx_property_id = 11

id = 73
tx_property_id = null
rx_property_id = 12

tx_property records:
id = 11
name = 'tx_aa'

id = 12
name = 'tx_bb'

rx_property records:
id = 11
name = 'rx_aa'

id = 12
name = 'rx_bb'

我希望将其中的 4 个检索为:

appliance.id = 70
appliance.tx_property_id = 11
appliance.rx_property_id = null
tx_property.id = 11
tx_property.name = 'tx_aa'

appliance.id = 71
appliance.tx_property_id = 12
appliance.rx_property_id = null
tx_property.id = 12
tx_property.name = 'tx_bb'

appliance.id = 72
appliance.tx_property_id = null
appliance.rx_property_id = 11
tx_property.id = 11
tx_property.name = 'rx_aa'

appliance.id = 73
appliance.tx_property_id = null
appliance.rx_property_id = 12
tx_property.id = 11
tx_property.name = 'rx_aa'

Answer 1

您可以在单个查询中将两个表连接到 appliance:

select * 
from appliance a
join rx_property r on r.id = a.rx_property_id
join tx_property t on t.id = a.tx_property_id

上述查询仅返回引用两个表的这些行。如果要获取所有行，使用left join:

select * 
from appliance a
left join rx_property r on r.id = a.rx_property_id
left join tx_property t on t.id = a.tx_property_id;

 id | rx_property_id | tx_property_id | id | name  | id | name  
----+----------------+----------------+----+-------+----+-------
 70 |             11 |                | 11 | rx_aa |    | 
 71 |             12 |                | 12 | rx_bb |    | 
 72 |                |             11 |    |       | 11 | tx_aa
 73 |                |             12 |    |       | 12 | tx_bb
(4 rows)    

您可以在选择列表中使用 coalesce() 来选择非空列，示例:

select 
    a.id, rx_property_id, tx_property_id,
    coalesce(r.name, t.name) as property_name
from appliance a
left join rx_property r on r.id = a.rx_property_id
left join tx_property t on t.id = a.tx_property_id

 id | rx_property_id | tx_property_id | property_name 
----+----------------+----------------+---------------
 70 |             11 |                | rx_aa
 71 |             12 |                | rx_bb
 72 |                |             11 | tx_aa
 73 |                |             12 | tx_bb
(4 rows)    

另请注意，我为表使用了别名，这通常会使查询更具可读性。

在 the documentation. 中阅读有关连接类型的更多信息