CODEWITHSUNDEEP
sqlms-access
Michael
Michael

Reputation: 1

Only use field as LEFT JOIN criteria if NOT NULL

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

Upvotes: 0

Views: 31

Answers (2)

jarlh
jarlh

Reputation: 44766

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)));

Upvotes: 1

Nenad J.
Nenad J.

Reputation: 341

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));

Upvotes: 0

Related Questions