Thempower Thempower -4 years ago 74
SQL Question

SQL LEFT JOIN WHERE not displaying right result

So I got this query:

Data structure:
Users
id---inlog----name----more stuff

llntoets
id---code----inlog----more stuff

oefeningen
id---speler---status----morestuff

(inlog and speler are always the same values for a user)

SELECT
// Some other stuff working
SUM(o.status) AS oefn
FROM users AS u
LEFT JOIN llntoets AS l
ON (u.inlog = l.inlog)
LEFT JOIN oefeningen AS o
ON (u.inlog = o.speler) AND o.status = 'afgewerkt'
WHERE
code = '$code'
GROUP BY l.inlog
ORDER BY klas ASC, klasnr ASC


Everything runs fine except 1 thing the oefn variable. It shows a number sometimes it shows the correct value and sometimes it shows a value that is much higher than it should be. Someone told me it could be because of the GROUP BY. Can someone help me pls?

It is supposed to count the total records from table oefeningen where status = 'afgewerkt' and where the speler is the inlog from users. Thanks, if you got other questions ask will try to explain more.

Answer Source

the SUM(o.status) in your query it is not supposed to count the total records of table oefeningen.
that sum is the sum of the values of all the joined rows that satisfy your criteria that can be a much higher number.
also note that applying the filter o.status = 'afgewerkt' you are performing a JOIN even if you wrote LEFT JOIN throghout the query.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download