I have a query that do 2 LEFT JOINs and 1 GROUP BY but I get missing results:

The following query sho开发者_运维知识库uld return around 1159 results but only returns around 880 results; If I only JOIN the "c" width "mm" I still get the 1159 results. Only when all 3 tables I get 880 results. I also used with no luck: RIGHT JOIN, RIGHT OUTER JOIN, LEFT OUTER JOIN, INNER JOIN. I tried with "()" and without "()" in the JOIN sintax no luck too.

Any help is appreciated

SELECT c.ccod, c.cetcm, c.cdscr, c.cpcul, c.cdent, mm.amexi,

Sum(CASE WHEN m.mcdmv=11 THEN m.mqtd END),

Sum(CASE WHEN m.mcdmv=2 THEN m.mqtd END),

Sum(CASE WHEN m.mcdmv=13 THEN m.mqtd END),
Sum(CASE WHEN m.mcdmv=14 THEN m.mqtd END),

MAX(CASE WHEN m.mcdmv=82 THEN m.mdata END)



FROM 
(zzz.ccc c 
LEFT JOIN zzz.mmmm mm ON c.crnp = mm.arncd)
LEFT JOIN zzz.mm m ON m.mrncd = c.crnp



WHERE 
((c.cetcm='xcd') OR
(c.cetcm='ewfwe') OR
(c.cetcm='fewfew') OR
(c.cetcm='fewf')) AND

(m.MDATA Between 20100101 And 20110406) AND

((m.mcdmv=11) OR 
(m.mcdmv=12) OR 
(m.mcdmv=13) OR 
(m.mcdmv=14)) AND

(mm.aarm=1)


GROUP BY c.ccod, c.cetcm, c.cdscr, c.cpcul, c.cdent, mm.amexi

ORDER BY c.ccod

As has been suggested, you need to move the criteria against the tables in the Left Joins into their respective ON clauses:

SELECT c.ccod, c.cetcm, c.cdscr, c.cpcul, c.cdent, mm.amexi
    , Sum(CASE WHEN m.mcdmv=11 THEN m.mqtd END) As Total11
    , Sum(CASE WHEN m.mcdmv=12 THEN m.mqtd END) As Total12
    , Sum(CASE WHEN m.mcdmv=13 THEN m.mqtd END) As Total13
    , Sum(CASE WHEN m.mcdmv=14 THEN m.mqtd END) As Total14
    , MAX(CASE WHEN m.mcdmv=82 THEN m.mdata END) As Max82
FROM (zzz.ccc c 
    LEFT JOIN zzz.mmmm mm 
        ON (c.crnp = mm.arncd
        And m.aarm = 1))
    LEFT JOIN zzz.mm m 
        ON (m.mrncd = c.crnp
            And m.mcdmv In(11,12,13,14,82)
            And m.MDATA Between #20100101# And #20110406#)
WHERE c.cetcm In('xcd','ewfwe','fewfew','fewf')
GROUP BY c.ccod, c.cetcm, c.cdscr, c.cpcul, c.cdent, mm.amexi
ORDER BY c.ccod

In addition, I've made use of the IN function which lets you pass a series of values. Btw, Access will likely also balk at not having column aliases for the columns aggregate functions. Here I've just used something simple.