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.

McNets
  • 23,979
  • 11
  • 51
  • 89
Bino
  • 23
  • 2

2 Answers2

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

McNets
  • 23,979
  • 11
  • 51
  • 89
-2
select a.bookid
from table1 a left join table2 b on a.userid = b.userid
group by a.bookid
having count(b.userid) = 0
Laurenz Albe
  • 61,070
  • 4
  • 55
  • 90
Kundan Bihari
  • 1
  • 3