beginner question!

wrehn · #1 (**permalink**) 10-21-09, 10:16

Hi! I need help with a query with LEFT JOIN

Kurs
kid (pk)

Kurstfillf
ktillfid (pk)
kid (sk)
year

I'm going to show all kurstillf per kurs during 2008! All the courses should be included

SELECT kurs.kursnamn, Count(kurstillf.kid) AS number
FROM kurs LEFT JOIN kurstillf ON kurs.kid=kurstillf.kid
WHERE Year=2008
GROUP BY kurs.kursnamn;

It works but how do i also get the "null" ones in my result. It seems like the left join takes away all with number=0. I'm using ms acces.

gvee · #2 (**permalink**) 10-21-09, 11:58

Two options

1) Move the where clause to the JOIN predecate

Code:

SELECT kurs.kursnamn
     , Count(kurstillf.kid) As number
FROM   kurs
 LEFT
  JOIN kurstillf
    ON kurs.kid = kurstillf.kid
   AND kurstillf.year = 2008 
GROUP
    BY kurs.kursnamn

2) Move the WHERE clause in to a derived table

Code:

SELECT kurs.kursnamn
     , Count(kurstillf.kid) As number
FROM   kurs
 LEFT
  JOIN (
        SELECT *
        FROM   kurstillf
        WHERE  year = 2008 
       ) As kurstillf
    ON kurs.kid = kurstillf.kid
GROUP
    BY kurs.kursnamn

r937 · #3 (**permalink**) 10-21-09, 13:20

another stellar post, george, i dunno if the punters appreciate it as much as i do but your SQL is gorgeous

keep up da good work, eh

stolze · #4 (**permalink**) 10-21-09, 14:29

The problem is that the WHERE clause contains a predicate "year = 2008". If a row in table KURS does not contain any matching row in KURSTFILLF, then the year column of that row will be populated with NULL. But NULL = 2008 evaluates to false in the predicate in the WHERE clause so that the row will be excluded from the result.