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?

Improve this question
0

2 Answers 2

Reset to default
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.

Improve this answer
1
  • 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
    Commented Dec 2, 2012 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
)
Improve this answer
2
  • 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.
    – Tanno
    Commented Dec 2, 2012 at 16:31
  • +1 for the alternative answers. Both LEFT JOIN / IS NULL and NOT EXISTS should outperform the LEFT JOIN / GROUP BY / COUNT()=0 variant. There is no need to count borrows for every student, only find those that haven't borrowed anything.
    – ypercubeᵀᴹ
    Commented Dec 2, 2012 at 17:54

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