Vishwanath jawalkar Vishwanath jawalkar - 2 months ago 7
SQL Question

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.

Answer

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