CODEWITHSUNDEEP
sqlsql-server
Vishwanath jawalkar
Vishwanath jawalkar

Reputation: 123

Display NULL if some value is not found from WHERE CLAUSE in SQL

I was trying to JOIN two tables based on their columns and a WHERE IN Clause.

SELECT DISTINCT
    t1.document_num, t2.file_name 
FROM 
    infocard_1 AS t1 
INNER JOIN 
    web_pub_subfile AS t2 on t1.info_card_id = t2.info_card_id
WHERE 
    lower(t1.vault_name) LIKE N'%su-spec-release%' AND 
    t2.file_name = 
       CASE 
          WHEN t2.file_name IN ('00350.dwg', '00924.dwg', '00960.dwg', '00973.dwg') 
             THEN t2.file_name
             ELSE NULL
       END

I am getting output in this format

document_num    file_name
-------------------------
SU-SH3A081      00960.DWG
SU-SH3A148      00973.DWG

But I would like to get 

document_num    file_name
-------------------------
null            00350.dwg
null            00924.dwg
SU-SH3A081      00960.DWG
SU-SH3A148      00973.DWG

Is there any way I can achieve this? Please help me.

Upvotes: 2

Views: 70

Answers (3)

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

Why do you have a CASE expression in WHERE? This makes it very hard to read. Use ANDor OR and parentheses when necessary to build your WHERE clause. Your clause simply translates to:

WHERE lower(t1.vault_name) LIKE N'%su-spec-release%' 
AND t2.file_name IN ('00350.dwg','00924.dwg','00960.dwg','00973.dwg')

To get records from a table even when they don't have a match in the other table, you'd outer join the other table.

SELECT DISTINCT ic.document_num, wps.file_name 
FROM web_pub_subfile wps
LEFT JOIN infocard_1 ic ON ic.info_card_id = wps.info_card_id
                        AND lower(ic.vault_name) LIKE N'%su-spec-release%'
WHERE wps.file_name IN ('00350.dwg','00924.dwg','00960.dwg','00973.dwg');

I replaced your alias names with something readable.

Upvotes: 0

Jaydip Jadhav
Jaydip Jadhav

Reputation: 12309

Try this:

SELECT distinct t1.document_num,t2.file_name 
FROM web_pub_subfile AS t2  
LEFT JOIN infocard_1 AS t1 on t1.info_card_id = t2.info_card_id AND 
                              lower(t1.vault_name) LIKE N'%su-spec-release%'
WHERE  t2.file_name IN ('00350.dwg','00924.dwg','00960.dwg','00973.dwg')

Upvotes: 1

S.Karras
S.Karras

Reputation: 1493

What you need is a RIGHT JOIN instead of an INNER JOIN. Read more about the types of JOINS here.

Upvotes: 2

Related Questions