CODEWITHSUNDEEP
sqlleft-joinisnull
MikeDes
MikeDes

Reputation: 28

LEFT JOIN ISNULL

trying to clean up a mess in a couple databases from a few years of bad decisions. Using my example below, we have a field from 'X' that links to a field from 'Y'. Problem is that 'X' was also used to link to a legacy system and holds a record for either system. 'Z' may contain an alternate link which i want to use IF the first connection is NULL...I'm lost of how to create this "efficiently" without running two separate queries into a temp table? Thanks.

FROM dbo.x X
LEFT JOIN   (SELECT Y.id, Z.varnum 
        FROM dbo.y Y
        LEFT JOIN dbo.Z
        ON Y.id = Z.varnum) W
ON X.id = ISNULL(W.id,W.varnum)

Upvotes: 0

Views: 1588

Answers (1)

Andre
Andre

Reputation: 1044

Use Coalesce function in select and two left joins

SELECT COALESCE(Y.ID, Z.VARNUM) FROM X
LEFT JOIN Y on X.ID = Y.ID
LEFT JOIN Z on X.ID = Z.VARNUM

Upvotes: 2

Related Questions