2

Hey I'm pretty new to SQL and I've come up with a problem that I haven't been able to solve

I'm trying to make a query to bring up ID's that have ALL null values and are located in another table.

DB looks like:

Table 1

BOOK ID| User ID            
1      |   1
1      |   2
2      |   17
2      |   16
3      |   17
3      |   16
4      |   3
4      |   16

Table 2

USER ID | Name
   1    | Jeff  
   2    | Bill   
   3    | Fred

The desired outcome would be to bring up Book IDs 2 and 3 as both IDs only have users that cannot be found in Table 2.

  • 1
    Book ID=4 has a User ID=16 that doesn't exists. It doesn't matter? – McNets Oct 15 at 14:57
  • 1
    @McNets Book ID 4 also has user ID 3, which is in table 2, so that exludes it. – Tony Hinkle Oct 15 at 14:59
2

I think the sentence is quite clear, using a left join to get null USER_ID's and returning those where count = 0;

SELECT
    BOOK_ID, COUNT(USERS.USER_ID) AS USERS
FROM
    BOOKS
LEFT JOIN 
    USERS
    ON USERS.USER_ID = BOOKS.USER_ID
GROUP BY
    BOOK_ID
HAVING COUNT(USERS.USER_ID) = 0;
BOOK_ID | USERS
------: | ----:
      2 |     0
      3 |     0

db<>fiddle here

  • Worked perfectly, thanks mate. – Bino Oct 16 at 1:12

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.