How to append data to a concatenation, from another table, only if that data is available

Question

I have 2 tables:

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

I want to make a query that displays the following:

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

SO it only adds Name_T2 to the concatenation, if an entry in Table2 exists that has L0INS.ID_INS = L1AVZ.FK_INS

I made this version that does display a L0INS entry for each Nume_AVZ append but also one that includes 'n/a'. I'd Like to remove the 'n/a' entry, if there is another one.

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

I sense there's something in the WHERE part missing, that I can't wrap my head around.

score 0 · Accepted Answer · answered Mar 02 '21 at 17:43

0

Isn't a LEFT JOIN simpler?

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;

answered Mar 02 '21 at 17:43

Gordon Linoff

1,242,037
58
646
786

1

Thank you so much for this! It works as expected. I never tried LEFT JOIN before. I'll have to read about it. – Dragos Dascalu Mar 02 '21 at 18:57

score 0 · Answer 2 · answered Mar 02 '21 at 17:45

0

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

answered Mar 02 '21 at 17:45

Sergey

4,719
1
6
11

Thank you for this! It works really nice now. Just as a side question; would it be too complicated to append all entries from AVZ to the corresponding INS entry? (it's a 1 to n relationship) – Dragos Dascalu Mar 02 '21 at 18:59

How to append data to a concatenation, from another table, only if that data is available

2 Answers2