SQL Server左JOIN不返回匹配行

Question

我有此查询，该查询已经加入了表，结果如预期。他们匹配第一表

     SELECT 
        A.RH6001 as Referencia, 
        A.RH6002 as UAP, 
        A.RH6030 as ConsumoWeek01, 
        A.RH6031 as ConsumoWeek02,
        IC130M.LLBLT1 as Stock,
        0 as PecasPorCaixa
    FROM AUTO.D805DATPOR.TRP060H AS A
    LEFT JOIN AUTO.D805DATPOR.IC130M IC130M 
    ON A.RH6001 = IC130M.LLPPN AND
         IC130M.LLSTLC =
             CASE A.RH6002
                  WHEN 'UAP1' THEN 'M1'
                  WHEN 'UAP2' THEN 'M2'
                  WHEN 'UAP3' THEN 'M3'
                  WHEN 'UAP4' THEN 'M4'
                  WHEN 'UAP5' THEN 'M5'
                  WHEN 'UAP6' THEN 'M6'
                  WHEN 'UAPP' THEN 'PROTOS'
                  WHEN 'EXT' THEN 'EXTR'
                END     
    WHERE (A.RH6001 Not Like 'FS%') 
    AND A.RH6030 <> 0
    ORDER BY Referencia DESC')

返回我1361行。

现在我有另一个查询

    SELECT 
        YDAUREP.AUD5CD AS Referencia,
        YDAUREP.AUQCON AS PecasPorCaixa
    FROM 
        AUTO.YSACHAPOR.YDAUREP YDAUREP
    WHERE (YDAUREP.AUD5CD Like 'M%' 
    AND YDAUREP.AUD5CD Not Like '%P%') 
    AND (YDAUREP.AUA0NB>1) 
    AND (YDAUREP.AUG6ST='O')
    ORDER BY YDAUREP.AUD5CD'    

返回785行

我想加入两个表格，其中第二个表上的所有行与第一表匹配，基本上应该返回我1361行。

我必须使用Referencia列加入两个表，问题是它在第二个表上是唯一的值，而在第一桌上，它不是，我认为这是因为它没有返回预期的行。<<<<<<<<<<<</p>

这是两个表加入的查询：

    SELECT 
        A.RH6001 as Referencia, 
        A.RH6002 as UAP, 
        A.RH6030 as ConsumoWeek01, 
        A.RH6031 as ConsumoWeek02,
        IC130M.LLBLT1 as Stock,
        YDAUREP.AUQCON AS PecasPorCaixa
    FROM AUTO.D805DATPOR.TRP060H AS A
    LEFT JOIN AUTO.D805DATPOR.IC130M IC130M 
    ON A.RH6001 = IC130M.LLPPN AND
         IC130M.LLSTLC =
             CASE A.RH6002
                  WHEN 'UAP1' THEN 'M1'
                  WHEN 'UAP2' THEN 'M2'
                  WHEN 'UAP3' THEN 'M3'
                  WHEN 'UAP4' THEN 'M4'
                  WHEN 'UAP5' THEN 'M5'
                  WHEN 'UAP6' THEN 'M6'
                  WHEN 'UAPP' THEN 'PROTOS'
                  WHEN 'EXT' THEN 'EXTR'
                END     
    LEFT JOIN AUTO.YSACHAPOR.YDAUREP YDAUREP
    ON YDAUREP.AUD5CD = A.RH6001
    WHERE (A.RH6001 Not Like 'FS%') 
    AND A.RH6030 <> 0
    AND (YDAUREP.AUD5CD Like 'M%' 
    AND YDAUREP.AUD5CD Not Like '%P%') 
    AND (YDAUREP.AUA0NB>1) 
    AND (YDAUREP.AUG6ST='O')
    ORDER BY Referencia DESC'

我要么做错了什么，要么我不知道如何使用加入。

我正在做一个左加入，结果返回的是579行，这意味着它将我从第二个表匹配的第二个表中返回我的所有行，我认为左联接会做出相反的状态。

无论哪种方式，即使我使用正确加入它也会返回相同的行...

我不确定，但是问题可能是我添加的条款的额外。

如何将我的1361行带有匹配的第二个表上的数据？

其余的可以是NULL ...

Answer 1

您在WHERE中引用YDAUREP而不处理NULL，将LEFT JOIN变成隐式INNER JOIN。将该部分移至ON：

SELECT A.RH6001 AS Referencia,
       A.RH6002 AS UAP,
       A.RH6030 AS ConsumoWeek01,
       A.RH6031 AS ConsumoWeek02,
       IC130M.LLBLT1 AS Stock,
       YDAUREP.AUQCON AS PecasPorCaixa
FROM AUTO.D805DATPOR.TRP060H AS A
     LEFT JOIN AUTO.D805DATPOR.IC130M AS IC130M ON A.RH6001 = IC130M.LLPPN
                                               AND IC130M.LLSTLC = CASE A.RH6002
                                                                        WHEN 'UAP1' THEN 'M1'
                                                                        WHEN 'UAP2' THEN 'M2'
                                                                        WHEN 'UAP3' THEN 'M3'
                                                                        WHEN 'UAP4' THEN 'M4'
                                                                        WHEN 'UAP5' THEN 'M5'
                                                                        WHEN 'UAP6' THEN 'M6'
                                                                        WHEN 'UAPP' THEN 'PROTOS'
                                                                        WHEN 'EXT' THEN 'EXTR'
                                                                   END
     LEFT JOIN AUTO.YSACHAPOR.YDAUREP AS YDAUREP ON YDAUREP.AUD5CD = A.RH6001
                                                AND YDAUREP.AUD5CD LIKE 'M%'
                                                AND YDAUREP.AUD5CD NOT LIKE '%P%'
                                                AND YDAUREP.AUA0NB > 1
                                                AND YDAUREP.AUG6ST = 'O'
WHERE A.RH6001 NOT LIKE 'FS%'
  AND A.RH6030 <> 0
ORDER BY Referencia DESC;

我还取消了额外的括号，因为它们不需要。