如果只有一列与第二列部分匹配，如何从第一张表返回两列? [英] How do I return two columns from first table if only one column partially matches with the second one?

问题描述

select art.artno, art.name  from Art 
left outer join store on art.artno =store.artno  
where art.Artno not in (select art.artno from store)

该查询应该是这样的，但似乎不起作用(在列名旁边我没有得到任何结果行).使用MSSQL2008.

The query is supposed to be like this but seems not working(I do not get any result rows beside the column names). Using a MSSQL 2008.

table art             table store                 EXPECTED RESULT 
artno   name          artno qty                  artno   name 
    1    xbox360          1   1                      2     XHW
    2    XHW              3   2                      5     PS2
    3    NETANDO          4   1
    5    PS2              6   3
    6    PS1 
    4    X1

如何编写查询以获取示例中所示的Expected?

How do I write a query to get the Expected out shown in the example?

如果有帮助，请告诉您表的大小是100多个K行.

Just to let you know the tables are 100plus K rows large if helps.

最后，阐明上述代码为何不起作用的一些方法将是有益的.我看了这个此链接似乎外部联接必须起作用，可能是我根本听不懂.

Finally some lights on why the above code does not work would be beneficial. I looked at this this link seems that outer join must work, may be I could not understand this at all.

我也尝试了full outer join，但没有帮助.使用except，我只能找到artno，但没有让我产生name列.

I tried with full outer join as well, did not help. Using except I was able to find just the artno but did not let me produce the name column.

推荐答案

另一种方法可能是

select
  a.`artno`,
  a.`name`
from
  art a
left join
  store s on s.artno=a.artno
where
  s.artno is null

在大型桌子上，第二种方法很可能会更好.

On large tables, the second approach would most likely be better.

这篇关于如果只有一列与第二列部分匹配，如何从第一张表返回两列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！