Postgresql:LEFT JOIN 不起作用

Question

我有两个具有多对多关系的表 tb1 和 tb2:

tb1:
id  | code
-------------
1   | 6%
2   | 12%

---

tb2:
id  | code
-------------
1   | code_001
2   | code_002

---

tb2_has_tb1:
id_tb2  | id_tb1   | money
---------------------------
1       | 1        | 250.0
1       | 2        | 300.0
2       | 1        | 100.0

我想要得到的结果是:

code_tb2  |code_tb1  | money
------------------------------
code_001  |6%        | 250.0
code_001  |12%       | 300.0
code_002  |6%        | 100.0
code_002  |12%       | 0.0   //----I want to display this row with zero if there is no join

所以我所做的是一个带有左连接的选择查询，如下所示:

SELECT tb2.code as code_tb2, tb1.code as code_tb1, money
FROM tb1 LEFT JOIN tb2_has_tb1 ON (tb1.id = id_tb1)
LEFT JOIN tb2 ON (tb2.id = id_tb2)

但我一直得到这个结果:

code_tb2  |code_tb1  | money
------------------------------
code_001  |6%        | 250.0
code_001  |12%       | 300.0
code_002  |6%        | 100.0

我错过了什么？

Answer 1

由于您希望保留 tb1 和 tb2 中所有可能的 id 值组合，一种选择是CROSS JOIN 这些表。然后，LEFT JOIN 将此结果添加到桥接表 tb2_has_tb1 以保留所有关系。

SELECT tb2.code AS code_tb2, tb1.code AS code_tb1,
    COALESCE(t.money, 0.0) AS money
FROM tb1 CROSS JOIN tb2
LEFT JOIN tb2_has_tb1 t
    ON tb1.id = t.id_tb1 AND tb2.id = t.id_tb2