Filtering Rows of a Table Based on Junction Table

I have the following situation (I have changed table names and scenario to simplify the explanation). There are four tables: ‘libraries’, ‘readers’ (id, library_id, first_name, last_name), ‘books’ (id, title) and a junction table ‘books_readers’ (id, reader_id, book_id) (the relationship between books and readers is many-to-many).

I am trying to build a mySQL query that lists all the readers from a particular library who have NOT signed out any books. Normally, these readers are not included in the books_readers table.

So far I have been successful:

SELECT r.id AS reader_id,
	r.first_name,
	r.last_name
FROM readers AS r
WHERE r.library_id = 2
AND r.id NOT IN (
	SELECT br.reader_id 
	FROM books_readers AS br
)	

The problem: Originally there was no junction table, just a column for book_id in the readers table, because at that time readers were only allowed 1 book or none. If there was no book, book_id had the value 0. When all that old information is transferred to the new junction table, those entries of 0 still exist. For the purposes of this problem, let’s assume that I can’t alter those entries.

So I really need to look for all readers who are either not in the books_readers table at all or who are in the books_readers table with a book_id of 0.

I can’t seem to include that final rule and make it work.

[quote=“WebMachine, post:1, topic:277073, full:true”]
readers who are either not in the books_readers table at all or who are in the books_readers table with a book_id of 0.[/quote]

SELECT r.id AS reader_id
     , r.first_name
     , r.last_name
  FROM readers AS r
LEFT OUTER
  JOIN books_readers AS br
    ON br.reader_id = r.id
 WHERE r.library_id = 2
   AND NULLIF(br.book_id,0) IS NULL

NULLIF magic :sunglasses:

oh wait… try this instead of NULLIF –

   AND COALESCE(br.book_id,0) = 0

COALESCE magic :sunglasses:

1 Like

Thank you! That worked like a charm. Now I have to hide away and disect your solution so that I totally understand what just happened. I think it’s time to take a refresher course on mySQL.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.