CODEWITHSUNDEEP
sqlsql-serversql-server-2005
Sin5k4
Sin5k4

Reputation: 1626

SQL join with case

How can I modify the join clause with a case clause; for example I want the table to join another column if column1 is null such as:

SELECT * FROM MYTABLE 
LEFT JOIN OTHERTABLE ON 
CASE WHEN MYTABLE.A IS NULL THEN MYTABLE.B = OTHERTABLE.A
ELSE MYTABLE.A IS NOT NULL THEN MYTABLE.A = OTHERTABLE.A

(totally made that up,sorry for syntax errors :))

Upvotes: 0

Views: 104

Answers (4)

bgs
bgs

Reputation: 3223

Just try below code :

SELECT * FROM MYTABLE 
LEFT JOIN OTHERTABLE ON OTHERTABLE.A = isnull(MYTABLE.A,MYTABLE.B)

Upvotes: 1

Free2Rhyme2k
Free2Rhyme2k

Reputation: 554

You can use isnull() or coalesce() to check the null value.

Upvotes: 0

Nenad Zivkovic
Nenad Zivkovic

Reputation: 18559

SELECT * FROM MYTABLE 
LEFT JOIN OTHERTABLE ON COALESCE(MYTABLE.A, MYTABLE.B) = OTHERTABLE.A

Upvotes: 1

gzaxx
gzaxx

Reputation: 17590

Try this one:

SELECT * 
FROM MyTable M
LEFT JOIN OtherTable O ON(CASE WHEN M.A IS NULL THEN M.B ELSE M.A END) = O.A

Upvotes: 4

Related Questions