Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2009
    Posts
    2

    Unanswered: beginner question!

    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.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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
    George
    Home | Blog

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by r937
    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

    Oh Rudy, you'll make me blush



    George
    Home | Blog

  6. #6
    Join Date
    Oct 2009
    Posts
    2
    thank you very much for the help guys! Very appriciated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •