How to list non existent items?
mysql, sql
Solution
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
Problem
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?