外部LEFT JOIN的SQL子查询

I need to select all rows from table A and select matching rows from table B if the table B rows match the table B select where clause. Here is my outer LEFT JOIN with sub query, but it fails. Can someone please help?

如果表B行与表B select where子句匹配,我需要从表A中选择所有行并从表B中选择匹配的行。这是我的外部LEFT JOIN与子查询,但它失败了。有人可以帮忙吗?

SELECT A.WRDWHSE,
       A.WRDRTID,
       A.WRDRPTLIN
FROM CWDDEV.WRTEMSTDP AS A 
LEFT JOIN (SELECT B.RHRTID,
                  B.RHRTE, 
                  B.RHSTAT, 
                  B.RHPRTT 
           FROM PIRF3.RTEHED
           WHERE B.RHWHSE=003 and 
                 ((B.RHSTSD =20151111 and B.RHSTST >=060000 ) or (B.RHSTSD=20151112 and B.RHSTST <=055959 )) and
                 B.RHTYPE NOT IN ('W') and 
                 (B.RHRTE NOT LIKE 'EMP%') and
                 B.RHSTAT IN (1, 3, 4, 5, 6)) AS B                                                     
ON A.WRDWHSE=B.RHWHSE and A.WRDRTID=B.RHRTE
WHERE A.WRDWHSE=003     
ORDER BY A.WRDRPTLIN

1 个解决方案

#1

Here are my initial thoughts inline with your query:

以下是我与您的查询内联的初步想法:

SELECT *
FROM
    CWDDEV.WRTEMSTDP A LEFT OUTER JOIN
    (
    SELECT
        B.RHWHSE, B.RHRTE,
        B.RHRTID, B.RHSTAT, B.RHPRTT /* are these going to be used? */
    FROM PIRF3.RTEHED B
    WHERE
            B.RHWHSE = 003 /* why all the leading zeros? are these numeric or a char? */
        AND (
               /* if these are really dates then these won't work */
               (B.RHSTSD = 20151111 AND B.RHSTST >= 060000)
            OR (B.RHSTSD = 20151112 AND B.RHSTST <= 055959)
        )
        AND B.RHTYPE NOT IN ('W')
        AND B.RHRTE NOT LIKE 'EMP%'
        AND B.RHSTAT IN (1, 3, 4, 5, 6)
    ) B_outer /* inside and outside have different names */
        ON A.WRDWHSE = B_outer.RHWHSE AND A.WRDRTID = B_outer.RHRTE
WHERE A.WRDWHSE = 003
ORDER BY A.WRDRPTLIN

EDIT: Based on the conversation below I'm willing to consider a DB2/AS400 bug. Your scenario for using a left join is very common. But here are some variations on the query that might work or help figure something out.

编辑:根据下面的对话,我愿意考虑DB2 / AS400错误。您使用左连接的方案非常常见。但是这里有一些可能有效或有助于解决问题的查询。

Eliminate redundant condition:

消除冗余条件:

SELECT *
FROM
    CWDDEV.WRTEMSTDP A LEFT OUTER JOIN
    (
    SELECT B.RHWHSE, B.RHRTE, B.RHRTID, B.RHSTAT, B.RHPRTT
    FROM PIRF3.RTEHED B
    WHERE
        (
               B.RHSTSD = 20151111 AND B.RHSTST >= 060000
            OR B.RHSTSD = 20151112 AND B.RHSTST <= 055959
        )
        AND B.RHTYPE <> 'W'
        AND B.RHRTE NOT LIKE 'EMP%'
        AND B.RHSTAT IN (1, 3, 4, 5, 6)
    ) B_outer
        ON A.WRDWHSE = B_outer.RHWHSE AND A.WRDRTID = B_outer.RHRTE
WHERE A.WRDWHSE = 3 AND A.WRDRTID = 73
ORDER BY A.WRDRPTLIN

Common table expression (CTE):

公用表表达式(CTE):

WITH ROUTES AS ( /* this might not be valid if your platform is old */
    SELECT
        B.RHWHSE, B.RHRTE, B.RHRTID, B.RHSTAT, B.RHPRTT
    FROM PIRF3.RTEHED B
    WHERE
        (
               B.RHSTSD = 20151111 AND B.RHSTST >= 060000
            OR B.RHSTSD = 20151112 AND B.RHSTST <= 055959
        )
        AND B.RHTYPE <> 'W'
        AND B.RHRTE NOT LIKE 'EMP%'
        AND B.RHSTAT IN (1, 3, 4, 5, 6)
)
SELECT *
FROM CWDDEV.WRTEMSTDP A LEFT OUTER JOIN ROUTES B
    ON A.WRDWHSE = B.RHWHSE AND A.WRDRTID = B.RHRTE
WHERE A.WRDWHSE = 3 AND A.WRDRTID = 73
ORDER BY A.WRDRPTLIN

Union:

SELECT
    A.WRDWHSE, A.WRDRTID, A.WRDRPTLIN,
    B.RHRTE, B.RHSTAT, B.RHPRTT
FROM
    CWDDEV.WRTEMSTDP A INNER JOIN PIRF3.RTEHED B
        ON A.WRDWHSE = B_outer.RHWHSE AND A.WRDRTID = B_outer.RHRTE
WHERE
        A.WRDWHSE = 3 AND A.WRDRTID = 73
    (
           B.RHSTSD = 20151111 AND B.RHSTST >= 060000
        OR B.RHSTSD = 20151112 AND B.RHSTST <= 055959
    )
    AND B.RHTYPE <> 'W'
    AND B.RHRTE NOT LIKE 'EMP%'
    AND B.RHSTAT IN (1, 3, 4, 5, 6)
UNION ALL
SELECT
    A.WRDWHSE, A.WRDRTID, A.WRDRPTLIN,
    NULL AS RHRTE, NULL AS RHSTAT, NULL AS RHPRTT
FROM CWDDEV.WRTEMSTDP A
WHERE A.WRDWHSE = 3 AND A.WRDRTID = 73 AND NOT EXISTS (
    SELECT 1
    FROM PIRF3.RTEHED B
    WHERE
            A.WRDWHSE = B.RHWHSE AND A.WRDRTID = B.RHRTE
        AND (
               B.RHSTSD = 20151111 AND B.RHSTST >= 060000
            OR B.RHSTSD = 20151112 AND B.RHSTST <= 055959
        )
        AND B.RHTYPE <> 'W'
        AND B.RHRTE NOT LIKE 'EMP%'
        AND B.RHSTAT IN (1, 3, 4, 5, 6)
    )
ORDER BY A.WRDRPTLIN

You could also try a view or a temp table if you really need to get it to work. It sounds like the nested loop you mentioned may be the best workaround for you.

如果你真的需要让它工作,你也可以尝试一个视图或临时表。听起来你提到的嵌套循环可能是最好的解决方法。

#1