CODEWITHSUNDEEP
sqlsql-server
user2378895
user2378895

Reputation: 431

Outer Join Excluding Rows

When I run the following query I have rows excluded from the results even though I know they should be there. I narrowed it down to the 5th join:
LEFT OUTER JOIN BDNDETL W ON
W.CODE = B.WP
If I remove that join and the columns that are referred to by alias "W", I get all of the rows that I expect. There are rows in W that don't have a match in B based on W.CODE = B.WP, but I thought the LEFT OUTER condition would catch these but it doesn't. It seems to just treat it like an INNER JOIN. 

SELECT
    p.p2 AS "Section",
    d.d9 AS "Alt WBS",
    p.p3 AS "Year",
    B.ca1 AS "WBS",
    concat(b.ca1,'/',b.ca2,'/',b.wp,b.descrip) AS "Work Package",
    B.WP,
    w.d9 AS "Cost Category",
    B.C3 AS "Provider",
    b.c1 AS "TType",
    w.d4 AS "WP GLA Cat",
    U.COSTSET,
    CONVERT(varchar, T.PD_FINISH, 101) period,
    SUM(A.ALLOWANCE1 + A.ALLOWANCE2 + A.ALLOWANCE3 + A.ALLOWANCE4 + A.ALLOWANCE5 + A.BASIC + A.BONUS + A.COMMODITY + A.DIRECT + A.ENHANCED + A.NIERS + A.OVERTIME + A.PENSION + A.PILON + A.REDUNDANCY) Value
FROM
    TPHASE A JOIN CAWP B ON
         B.PROGRAM = A.PROGRAM AND B.CAWPID = A.CAWPID
            JOIN MULTPROG X ON
                A.PROGRAM = X.SUBPROGRAM
            JOIN PROGRAM P ON
                A.PROGRAM = P.PROGRAM
            JOIN BDNDETL D ON
                D.CODE = B.CA1
            LEFT OUTER JOIN BDNDETL W ON
                W.CODE = B.WP
            JOIN COSTDETL U ON
                A.CLASS = U.CLASS
            JOIN RCUTOFF T ON
                A.DF_DATE BETWEEN T.PD_START AND T.PD_FINISH
WHERE
    X.MASTER = @master AND
    D.BREAKFILE = @wbsfile AND
    U.INSTANCE = @costdetlid AND
    T.INSTANCE = @cutoffid AND
    W.BREAKFILE = @wpbdn
GROUP BY
    p.p2,
    d.d9,
    p.p3,
    B.ca1,
    concat(b.ca1,'/',b.ca2,'/',b.wp,b.descrip),
    B.WP,
    w.d9,
    B.C3,
    b.c1,
    w.d4,
    U.COSTSET,
    T.PD_FINISH
;

Upvotes: 1

Views: 79

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269713

This condition in the WHERE clause:

    W.BREAKFILE = @wpbdn

fails when there is no match. Why? Because W.BREAKFILE is NULL. You should move it to the appropriate ON clause:

       LEFT OUTER JOIN BDNDETL W ON
            W.CODE = B.WP AND W.BREAKFILE = @wpbdn

And, a piece of advice. When you are mixing inner and left joins, I strongly recommend that you put all the inner joins first followed by the left joins:

FROM TPHASE A JOIN
     CAWP B
     ON B.PROGRAM = A.PROGRAM AND B.CAWPID = A.CAWPID JOIN
     MULTPROG X
     ON A.PROGRAM = X.SUBPROGRAM JOIN
     PROGRAM P
     ON A.PROGRAM = P.PROGRAM JOIN
     BDNDETL D
     ON D.CODE = B.CA1 JOIN
     COSTDETL U
     ON A.CLASS = U.CLASS JOIN
     RCUTOFF T
     ON A.DF_DATE BETWEEN T.PD_START AND T.PD_FINISH LEFT JOIN
     BDNDETL W
     ON W.CODE = B.WP

This makes the logic much easier to follow. The idea is that the inner joins do the filtering, then the left join says "keep all those rows and add these additional columns if they match".

Upvotes: 3

amow
amow

Reputation: 2223

you have W.BREAKFILE = @wpbdn in your where clause, the null value of left join can't pass this filter.

Upvotes: 3

Related Questions