Left join not working

[steeffie]

I have a query with a left join that is acting rather strange. It returns 1 row, though it should return 3 rows (that is what you get if i would leave out the left join and its columns). What is even more remarkable is that it returns 2 rows when changing Left to Inner join. I think i know a whole lot of SQL but this goes above my baseballcap (as we say in Holland).

Here is the query:

SELECT DP.Omschrijving AS Omschrijving, 4 AS Niveau, IIf(Nz(DP.ProcesStapID,0)>0,(100*P.ProcesID + DP.ProcesStapID),P.ProcesID) AS ID, Sum(Nz(V.WaardeOchtend,0)) AS VolumeOchtend, Sum(Nz(V.WaardeMiddag,0)) AS VolumeMiddag, Sum(Nz(V.WaardeAvond,0)) AS VolumeAvond, Sum(Nz(V.WaardeNacht,0)) AS VolumeNacht

FROM ((Proces AS P INNER JOIN ProcesProductie AS DP ON DP.ProcesID = P.ProcesID) INNER JOIN SubSelectieVolume AS V ON V.ProcesCode = DP.ProcesCode AND V.ProcesStapID = DP.ProcesStapID) 
 
LEFT JOIN SubSelectiePlanningProductie AS PL ON (PL.SorteerCentrumID = V.SorteerCentrumID AND PL.ProcesCode = V.ProcesCode AND PL.PeriodeID = V.PeriodeID AND PL.Datum = V.Datum AND PL.ProcesStapID = V.ProcesStapID) 
 
GROUP BY V.SorteerCentrumID, DP.Omschrijving, P.ClusterID, P.ProcesID, P.SortPulldown, DP.ProcesStapID;

 

[SQL_Hell]

Hoi,

Hoe gaat het?

Well that's the limit of my Dutch speaking abilites

Remove the group by and sums and re-run, I bet you will see your 3 rows, can't group by null so the results will be ommitted

 

[steeffie]

Very well spoken (babelfish?)

Of course I did that first to see what I would expect the outcome to be.

As all of the group by fields will not be null (as they are from the left side of the joins) that is not the problem.

 

[SQL_Hell]

Lol not bablefish, I spent some time working in Holland in 2008.

Ok my bad, I didn't actually check which side the group by columns were on.

I supect it's something to do with all the fields you are joining on, is there not a primary / forreign key you can use to make the join and put the other columns in a where or having clause? Or are all these fields part of a composite key?

 

[steeffie]

What possible query could you make where a left join would return LESS records than an inner join? I'll even make it more complex:
1. When i have 3 rows on the left side and 2 on the right side of the left join it returns 1 row (which make me wanna cry). And as I said it returns 2 when I use inner join.
2. When i have 6 rows on the left side and 4 on the right side of the left join it returns 6 rows as it should. Even if it contains the 3 rows left and the 2 rows right as stated in 1.