user2378895 user2378895 - 2 months ago 7
SQL Question

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
;

Answer

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".

Comments