user3123109 user3123109 - 3 months ago 7
SQL Question

MS Access SQL Records Disappearing in LEFT JOIN that shouldn't

I am really not understanding why this

LEFT JOIN
is not working the way it should. There are supposed to be 99 records coming from
cleaning_zzz_2016
as is the case when I remove the
LEFT JOIN
. However, when I add the
LEFT JOIN
it drops down to 96. Some of the
CoNumber
don't exist in the
cleaning_zzz_sales
table, so I would expect the those fields to just be blank and the
s.
data to remain; however, it is removing them. I think this may be due to the
WHERE s.Rep = 'ZZZ'
, but I am not 100% sure or how to get around the issue.

SELECT j.CoNumber, j.CoName, s.Selling, s.Positions, s.Plans, '' AS Review, j.St, j.CE, j.SI, j.Tgt, ROUND(MAX(j.Comm) * 100) AS [Max of Comm], ROUND(MAX(j.Comm2) * 100) AS [Max of Comm2]
FROM cleaning_zzz_2016 AS j
LEFT JOIN cleaning_zzz_sales AS s
ON j.CoNumber= s.CoNumber
WHERE s.Rep = 'ZZZ' AND s.RepYear = 2016
GROUP BY j.CoNumber, j.CoName, j.St, j.CE, j.SI, j.Tgt, s.Selling, s.Positions, s.Plans
ORDER BY j.CoNumber;

Answer

Any conditions in the where clause will filter out rows outright. You want to add all conditions on the left joined table directly in the join condition itself. With MS Access, I believe you need to be careful with the parenthesis when you have multiple join conditions.

So I think that the query would look like this:

SELECT j.CoNumber, j.CoName, s.Selling, s.Positions, s.Plans, '' AS Review, j.St, j.CE, j.SI, j.Tgt, ROUND(MAX(j.Comm) * 100) AS [Max of Comm], ROUND(MAX(j.Comm2) * 100) AS [Max of Comm2]
FROM cleaning_zzz_2016 AS j 
LEFT JOIN cleaning_zzz_sales AS s 
  ON (j.CoNumber= s.CoNumber
  AND s.Rep = 'ZZZ' 
  AND s.RepYear = 2016)
GROUP BY j.CoNumber, j.CoName, j.St, j.CE, j.SI, j.Tgt, s.Selling, s.Positions, s.Plans
ORDER BY j.CoNumber;
Comments