我有一个包含4个表的Postgresql数据库：
表A.
--------------------------- | ID | B_ID | C_ID | D_ID | --------------------------- | 1 | 1 | NULL | NULL | --------------------------- | 2 | NULL | 1 | NULL | --------------------------- | 3 | 2 | 2 | 1 | --------------------------- | 4 | NULL | NULL | 2 | ---------------------------
表B.
------------- | ID | DATA | ------------- | 1 | 123 | ------------- | 2 | 456 | -------------
表C.
------------- | ID | DATA | ------------- | 1 | 789 | ------------- | 2 | 102 | -------------
表D.
------------- | ID | DATA | ------------- | 1 | 654 | ------------- | 2 | 321 | -------------
我正在尝试检索已加入表B中的数据和表C中的数据的结果集,但前提是其中一个booth ID不为null.
SELECT "Table_A"."ID","Table_A"."ID_B","Table_A"."ID_C","Table_A"."ID_D","Table_B"."DATA","Table_C"."DATA"
FROM "Table_A"
LEFT JOIN "Table_B" on "Table_A"."ID_B" = "Table_B"."ID"
LEFT JOIN "Table_C" on "Table_A"."ID_C" = "Table_C"."ID"
WHERE "Table_A"."ID_B" IS NOT NULL OR "Table_A"."ID_C" IS NOT NULL;
这是推荐还是我应该在多个查询中更好地拆分它？
有没有办法在这些表之间进行内部联接？
我期望的结果是：
------------------------------------------------- | ID | ID_B | ID_C | ID_D | DATA (B) | DATA (C) | ------------------------------------------------- | 1 | 1 | NULL | NULL | 123 | NULL | ------------------------------------------------- | 2 | NULL | 1 | NULL | NULL | 789 | ------------------------------------------------- | 3 | 2 | 2 | NULL | 456 | 102 | -------------------------------------------------
编辑：ID_B,ID_C,ID_D是表table_b,table_c,table_d的外键
解决方法
WHERE“Table_A”.“ID_B”不是NULL或“Table_A”.“ID_C”不是NULL;可以用B和C表中的相应子句替换：WHERE“Table_B”.“ID”不是NULL或“Table_C”.“ID”不是NULL; .如果table_a.id_b和table_a.id_c不是B和C表的FK,这也可以工作.否则,具有{5,5,5}的table_a行将从B和C表中检索两个NULL行.
SELECT ta."ID" AS a_id,ta."ID_B" AS b_id,ta."ID_C" AS c_id,ta."ID_D" AS d_id,tb."DATA" AS bdata,tc."DATA" AS cdata
FROM "Table_a" ta
LEFT JOIN "Table_B" tb on ta."ID_B" = tb."ID"
LEFT JOIN "Table_C" tc on ta."ID_C" = tc."ID"
WHERE tb."ID" IS NOT NULL OR tc."ID" IS NOT NULL
;
