Server联接不返回任何行

Question

所以我的数据库中有这样的情况：

SELECT A.No_ as itemno,A.Description as description from
[dbo].[PRUEBAS$Item] A
WHERE (A.No_ LIKE  '020212601%' COLLATE Modern_Spanish_100_CI_AI 
or A.Description LIKE '%020212601%' COLLATE  Modern_Spanish_100_CI_AI);

只返回一行

+-----------+-------------+
|  itemno   | description |
+-----------+-------------+
| 020212601 | BLA BLA     |
+-----------+-------------+

我的第二个表返回如下：

select * from [dbo].[PRUEBAS$Item Cross Reference] B 
where B.[Item No_] LIKE  '020212601%' COLLATE Modern_Spanish_100_CI_AI ;

+-----------+----------------------+---------------------+--+
| Item No_  | Cross-Reference Type | Cross-Reference No_ |  |
+-----------+----------------------+---------------------+--+
| 020212601 |                    2 |                     |  |
| 020212601 |                    2 |           020212601 |  |
| 020212601 |                    2 |                     |  |
| 020212601 |                    2 |           020212601 |  |
+-----------+----------------------+---------------------+--+

现在我加入了这两张桌子：

select A.No_ as itemno,A.Description as description, A.[Producto M3] as m3item  from
[dbo].[PRUEBAS$Item] A left outer join [dbo].[PRUEBAS$Item Cross Reference] B 
on A.No_ = B.[Item No_] 
WHERE (A.No_ LIKE  '020212601%' COLLATE Modern_Spanish_100_CI_AI 
or A.Description LIKE '%020212601%' COLLATE  Modern_Spanish_100_CI_AI 
or B.[Cross-Reference No_] LIKE  '020212601%' COLLATE Modern_Spanish_100_CI_AI)

我得到：

+-----------+-------------+--------+
|  itemno   | description | m3item |
+-----------+-------------+--------+
| 020212601 | BLA BLA     |      0 |
| 020212601 | BLA BLA     |      0 |
| 020212601 | BLA BLA     |      0 |
| 020212601 | BLA BLA     |      0 |
+-----------+-------------+--------+

这意味着，我的相关表有4行。

接下来我要做的是在WHERE子句中添加一个过滤器，因为我不希望第二个表中有一些数据：

select A.No_ as itemno,A.Description as description, A.[Producto M3] as m3item  from
[dbo].[PRUEBAS$Item] A left outer join [dbo].[PRUEBAS$Item Cross Reference] B 
on A.No_ = B.[Item No_] 
WHERE (A.No_ LIKE  '020212601%' COLLATE Modern_Spanish_100_CI_AI 
or A.Description LIKE '%020212601%' COLLATE  Modern_Spanish_100_CI_AI 
or B.[Cross-Reference No_] LIKE  '020212601%' COLLATE Modern_Spanish_100_CI_AI) 
and (B.[Cross-Reference Type] = 3)

这根本不返回任何行！我本来希望得到一行，因为我使用的是左外部联接。

我错过了什么？我需要得到一行，即使在第二个表中没有记录。

Answer 1

我相信你想要的问题是：

select A.No_ as itemno,A.Description as description, A.[Producto M3] as m3item 
from [dbo].[PRUEBAS$Item] A left outer join
     [dbo].[PRUEBAS$Item Cross Reference] B 
     on A.No_ = B.[Item No_] and
        B.[Cross-Reference Type] = 3
WHERE (A.No_ LIKE  '020212601%' COLLATE Modern_Spanish_100_CI_AI) or 
      (A.Description LIKE '%020212601%' COLLATE  Modern_Spanish_100_CI_AI) or 
      (B.[Cross-Reference No_] LIKE  '020212601%' COLLATE Modern_Spanish_100_CI_AI) ;

你在第二张桌子上有一个复杂的情况。条件= 3需要进入on子句，因为它引用了所有行。另一个条件作为or条件的一部分是可选的，因此它可以保留在WHERE子句中。

Answer 2

尝试将WHERE子句中第二个表的限制移到联接本身的ON子句中：

SELECT
    A.No_ AS itemno,
    A.Description AS description,
    A.[Producto M3] AS m3item
FROM [dbo].[PRUEBAS$Item] A
LEFT JOIN [dbo].[PRUEBAS$Item Cross Reference] B
    ON A.No_ = B.[Item No_] AND
       B.[Cross-Reference No_] LIKE '020212601%' COLLATE Modern_Spanish_100_CI_AI
WHERE
    (A.No_ LIKE '020212601%' COLLATE Modern_Spanish_100_CI_AI OR
     A.Description LIKE '%020212601%' COLLATE  Modern_Spanish_100_CI_AI) AND
    [Cross-Reference Type] = 3;

当前查询的问题是，出现在WHERE子句中的对第二个表的限制可能导致在查询完成之前过早地筛选出不匹配的记录。上述建议应能解决这一问题。