Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
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. –  Max Vernon Dec 2 '12 at 14:46
    
@Tanno Please provide sample data and expected result. –  sufleR Dec 2 '12 at 14:55

2 Answers 2

up vote 6 down vote accepted

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.

share|improve this answer
    
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. –  a_horse_with_no_name 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) –  a_horse_with_no_name 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

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
)
share|improve this answer
    
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. –  a_horse_with_no_name 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. –  Max Vernon Dec 2 '12 at 14:43

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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