3

Alright, I'm trying to make a query for a library that will show which students have never borrowed a book. For this, I have done the following:

SELECT
    LEERLINGEN.LLNR,
    LEERLINGEN.VOORNAAM,
    LEERLINGEN.TUSSENVOEGSEL,
    LEERLINGEN.ACHTERNAAM,
    LEERLINGEN.KLAS,
    COUNT(*) AS AANTAL
FROM
    UITLENINGEN
    INNER JOIN LEERLINGEN ON UITLENINGEN.LLNR = LEERLINGEN.LLNR
GROUP BY
    LEERLINGEN.LLNR,
    LEERLINGEN.VOORNAAM,
    LEERLINGEN.TUSSENVOEGSEL,
    LEERLINGEN.ACHTERNAAM,
    LEERLINGEN.KLAS
HAVING
    COUNT(*) = 0;

This doesn't seem to work for some reason, as all it does is create an empty table when I click Execute.

What did I do wrong here?

2
  • as SQL Kiwi said, we need at least some sample data. The answers provided below do work, however only if the data supports the desired result.
    – Hannah Vernon
    Dec 2 '12 at 14:46
  • @Tanno Please provide sample data and expected result.
    – sufleR
    Dec 2 '12 at 14:55
6

You need to use an outer join, otherwise you won't get those students back that did not borrow a book.

Then you need to count() on the students table, not "the group" (which is done when you use (*))

SELECT
    LEERLINGEN.LLNR,
    LEERLINGEN.VOORNAAM,
    LEERLINGEN.TUSSENVOEGSEL,
    LEERLINGEN.ACHTERNAAM,
    LEERLINGEN.KLAS,
    COUNT(LEERLINGEN.LLNR) AS AANTAL
FROM
    LEERLINGEN
    LEFT OUTER JOIN UITLENINGEN ON UITLENINGEN.LLNR = LEERLINGEN.LLNR
GROUP BY
    LEERLINGEN.LLNR,
    LEERLINGEN.VOORNAAM,
    LEERLINGEN.TUSSENVOEGSEL,
    LEERLINGEN.ACHTERNAAM,
    LEERLINGEN.KLAS
HAVING
    COUNT(UITLENINGEN.LLNR) = 0;

I dont' know MS-Access, but I assume it supports outer joins using standard SQL.

5
  • I still don't get any results back. Replacing COUNT(LEERLINGEN.LLNR) with COUNT(UITLENINGEN.LLNR) didn't help either.
    – Tanno
    Dec 2 '12 at 13:06
  • @Tanno: sorry - copy and paste error. The outer join was in the wrong direction. See my edit, it's essentially the same as sufleR's answer. Dec 2 '12 at 13:37
  • Nope. Just like SulfeR's answer (explanation on his answer), this one didn't work either.
    – Tanno
    Dec 2 '12 at 13:40
  • @Tanno: then MS Access doesn't work as any other DBMS, it sure works using PostgreSQL (see here sqlfiddle.com/#!12/5d2c1/1) and Oracle (see here: sqlfiddle.com/#!4/5d2c1/1) Dec 2 '12 at 13:46
  • 1
    I have found the issue: turns out the database I was given did not have a single student in it who had never borrowed a book. Following some modifications to the LEERLINGEN table, this query is the one that worked. Thanks!
    – Tanno
    Dec 2 '12 at 16:31
5

After translation of table names i think that this will work:

SELECT
    LEERLINGEN.LLNR,
    LEERLINGEN.VOORNAAM,
    LEERLINGEN.TUSSENVOEGSEL,
    LEERLINGEN.ACHTERNAAM,
    LEERLINGEN.KLAS

FROM
    LEERLINGEN 
  LEFT OUTER JOIN UITLENINGEN ON UITLENINGEN.LLNR = LEERLINGEN.LLNR
WHERE 
    UITLENINGEN.LLNR IS NULL;

EDIT:
You are looking for LEERLINGEN where there is no UITLENINGEN at all. Check this alternative:

SELECT
    LEERLINGEN.LLNR,
    LEERLINGEN.VOORNAAM,
    LEERLINGEN.TUSSENVOEGSEL,
    LEERLINGEN.ACHTERNAAM,
    LEERLINGEN.KLAS

FROM
    LEERLINGEN 
WHERE NOT EXISTS (
    SELECT 1 FROM UITLENINGEN WHERE LLNR = LEERINGEN.LLNR
)
7
  • Didn't work. Probably because LLNR is the ID number of a student; for example my number would be 698. If UITLENINGEN.LLNR would be null, it'd show the records of the student with the ID 0.
    – Tanno
    Dec 2 '12 at 13:41
  • try to use different column then LLNR. This query should return all records from LEERLINGEN which doesn't have any record in UITLENINGEN.
    – sufleR
    Dec 2 '12 at 13:44
  • The second query should do exactly the same thing as the first one. I get the felling that Access doesn't properly handle outer joins. Dec 2 '12 at 13:51
  • @a_horse_with_no_name I know that result should be the same but ms access for sure have NOT EXISTS clause(allenbrowne.com/subquery-01.html) so maybe last query will work as msaccess solution.
    – sufleR
    Dec 2 '12 at 13:54
  • Access most definitely supports all the join styles listed here and the exists clause. The only gotcha could be Access requires brackets around the joins; however since you are only dealing with a single join I doubt that is the issue.
    – Hannah Vernon
    Dec 2 '12 at 14:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.