CODEWITHSUNDEEP
phpmysql
Roeliee
Roeliee

Reputation: 221

Complicated mysql join for student listing

I have this problem with my query. The goal of the query is to display all students, whether they are in the aanweezigheid table or not.

This is my query:

SELECT 
   s.studentNaam
   , s.studentAchterNaam
   , s.studentStamNummer
   , s.klasID
   , k.klasNaam
   , k.klasID
   , a.studentID
   , a.aanwezigTijdAan
   , a.aanwezigTijdAf
   , a.aanwezigDag
   , a.aanwezigStatus 

FROM studenten AS s 
   LEFT JOIN klassen AS k ON s.klasID=k.klasID 
   LEFT JOIN aanweezigheid AS a ON a.studentID=s.studentID
WHERE k.klasNaam = 'MD2a' 
   AND a.aanwezigDag='2012-08-28'
ORDER BY s.studentAchterNaam ASC

Any ideas?

Upvotes: 1

Views: 85

Answers (2)

Omesh
Omesh

Reputation: 29121

Move your WHERE conditions to LEFT JOIN ON clause:

SELECT ...
FROM studenten AS s
   LEFT JOIN klassen AS k
      ON s.klasID=k.klasID
         AND k.klasNaam = 'MD2a'
   LEFT JOIN aanweezigheid AS a
      ON a.studentID=s.studentID
         AND a.aanwezigDag='2012-08-28'
ORDER BY s.studentAchterNaam ASC;

Upvotes: 1

davek
davek

Reputation: 22925

just move your "a" dependency into the ON term. That way you won't be filtering anything out in your WHERE:

SELECT 
   s.studentNaam
   , s.studentAchterNaam
   , s.studentStamNummer
   , s.klasID
   , k.klasNaam
   , k.klasID
   , a.studentID
   , a.aanwezigTijdAan
   , a.aanwezigTijdAf
   , a.aanwezigDag
   , a.aanwezigStatus 

FROM studenten AS s 
   LEFT JOIN klassen AS k ON s.klasID=k.klasID 
   LEFT JOIN aanweezigheid AS a ON a.studentID=s.studentID
              AND a.aanwezigDag='2012-08-28'
WHERE k.klasNaam = 'MD2a' 

ORDER BY s.studentAchterNaam ASC

Upvotes: 0

Related Questions