如何将来自另一个表的数据附加到连接中，仅当该数据可用时

Question

我有两个表:

L0INS (ID_INS, Nume_INS)
1 ANB
2 ENEL
3 DIGI
L1AVZ (ID_AVZ, FK_INS, Nume_AVZ)
1 1 APA
2 1 CAN
3 1 HID
4 2 ELE

我想做一个查询，显示以下内容:

Query1 (ID_INS, CONCAT(Nume_INS, Nume_AVZ) )
1 ANB, APA
1 ANB, CAN
1 ANB, HID
2 ENEL, ELE
3 DIGI, n/a

因此，如果表2中存在具有L0INS的条目，则仅将Name_T2添加到串联中。Id_ins = l1avz。FK_INS

我制作了这个版本，它确实为每个Nume_AVZ追加显示了一个L0INS条目，但也包含了'n/a'。我想删除'n/a'条目，如果有另一个。

SELECT DISTINCT     "L0INS"."ID_INS", 
COALESCE ( "NUME_INS", 'n/a' ) || ', ' || CASE WHEN "L1AVZ"."FK_INS" = "L0INS"."ID_INS" THEN "L1AVZ"."NUME_AVZ" ELSE 'n/a' END "INS_SELECT", 
LOWER ( COALESCE ( "NUME_INS", 'n/a' ) || ', ' || CASE WHEN "L1AVZ"."FK_INS" = "L0INS"."ID_INS" THEN "L1AVZ"."NUME_AVZ" ELSE 'n/a' END ) "INS_SEARCH" 
FROM        "L1AVZ", "L0INS" 
WHERE   "L0INS"."ID_INS" IS NOT NULL

我感觉WHERE部分缺少了什么，我无法理解。

Answer 1

LEFT JOIN不是更简单吗?

SELECT l1."ID_INS", 
("NUME_INS" || ', ' || OALESCE(l0."NUME_AVZ", 'n/a') AS INS_SEARCH"
FROM "L1AVZ" l1 LEFT JOIN
"L0INS" lO
ON l1.id_ins = l0.fk_ins;

Answer 2

SELECT L.ID_INS,L.Nume_INS +','+COALESCE(AVZ.Nume_AVZ,'N/A')AS X
FROM L0INS AS L
LEFT JOIN L1AVZ AS AVZ ON L.ID_INS=AVZ.FK_INS