如果在SQL中的WHERE子句中没有找到某个值，则显示NULL。

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

我试图根据它们的列和WHERE IN子句连接两个表。

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.

我有什么办法可以做到这一点吗?请帮助我。

3 个解决方案

#1

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

#2

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

您需要的是右连接，而不是内连接。在这里阅读有关连接类型的更多信息。

#3

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:

为什么在哪里有一个CASE表达式?这使得阅读非常困难。必要时使用and或圆括号来构建WHERE子句。你的条款简单地翻译为:

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.

我用一些可读的东西替换了你的别名。

#1