How to list non existent items?

Question

Hi, the title might be a little bit confusing, let me explain, ;) I have 3 tables:

[names]
n_id;name
1;Jeff
2;Adam

[books]
b_id;title
1;Book1
2;Book2

[read]
n_id;b_id

The table [read] is a table with read books. if Adam reads "Book1" the item in [read] looks like this:

2;1

so far, so good. Now, is there a way to know which books werent read by a person? We know that only Adam read a book "Book1", so the query should output something like this:

n_id;name;b_id;title
1;Jeff;1;Book1
1;Jeff;2;Book2
2;Adam;2;Book2

is it possible to do this in 1 query or do I need some scripting?

Answer 1

You need a cross join to generate all pairs of name vs book, and then join with the read table and check where the join fails.

SELECT names.n_id, names.name, books.b_id, books.title
FROM names
CROSS JOIN books 
LEFT JOIN read
ON names.n_id = read.n_id AND books.b_id = read.b_id
WHERE read.n_id IS NULL

Answer 2

You would do a cartesian join between names and books to get all possible name/book combinations, then minus the ones that have been read:

SELECT n_id, b_id
FROM names, books
MINUS
SELECT n_id, b_id
FROM read

Others have suggested doing a cross join and a left join, which would also work perfectly well. Might want to try both to see which is faster in a real-world scenario - I suspect the left join others suggested would be faster, but not really sure.

Answer 3

You can use a CROSS JOIN to get all possible combinations of names and books and then use a LEFT JOIN on read with IS NULL to remove rows that exist there.

The LEFT JOIN returns NULL for all joined columns where no rows exist, so checking if r.n_id IS NULL removes those rows where the join actually found rows in read.

SELECT n.n_id, n.name, b.b_id, b.title
FROM names n
CROSS JOIN books b
LEFT JOIN read r ON ( r.n_id = n.n_id AND r.b_id = b.b_id )
WHERE r.n_id IS NULL

Answer 4

Your question was, "Is there a way to know which books werent read by a person?" But your sample query results showed the answer to the question, "Is there a way to know which books weren't read by all people?"

If you're really looking just to run the query for a specific person, something like this should work:

SELECT b_id, title
FROM books  --You said you're just looking for books
WHERE b_id NOT IN
  (SELECT b_id
   FROM read
   WHERE n_id = @names_id) --pass in the names_id as a parameter