Only use field as LEFT JOIN criteria if NOT NULL-CodePudding

Im fairly new to SQL development and currently struggling with this LEFT JOIN for my Access application:

SELECT v.*, t.uuid
FROM V_Feedback v 
LEFT JOIN T_Feedback as t
ON (v.street = t.street AND v.nr = t.nr AND v.affix = t.affix);

It's working fine but my problem is that it only displays the t.uuid value if an affix exists. If v.affix and t.affix are empty it should still join t.uuid to the other columns. Is there an easy way to check whether both v.affix and t.affix are empty, and, if so, just join based on v.street and v.nr?

Thanks for your help in advance. BR, Michael

CodePudding user response：

To also include when v.affix and t.affix are empty, add or (v.affix is null and t.affix is null), i.e.:

SELECT v.*, t.uuid
FROM V_Feedback v 
LEFT JOIN T_Feedback as t
ON (v.street = t.street AND v.nr = t.nr
    AND (v.affix = t.affix or (v.affix is null and t.affix is null)));

CodePudding user response：

Check v.affix and t.affix for null:

SELECT v.*, t.uuid
FROM V_Feedback v 
LEFT JOIN T_Feedback as t
ON (v.street = t.street 
      AND v.nr = t.nr 
      AND (v.affix = t.affix OR v.affix is null OR t.affix is null));