In a Many to Many relationship, how can records collectively owned be selected.

Question

I'm sure I'm missing the correct terminology here, but for this example many-to-many relationship:

A user has many books. A book is owned by many users.

What's the best way to see all books owned by both of two users?

Right now I'm just using this:

SELECT * FROM books 
WHERE book_id IN 
    (SELECT book_id FROM user_has_book WHERE user_id = 'joe' AND book_id IN 
    (SELECT book_id FROM user_has_book WHERE user_id = 'frank'))

I know that can't be the best way to do this - right? I'm using MySQL

Answer 1

SELECT b.*
FROM books b
INNER JOIN
  (SELECT book_id, COUNT(*) as cnt 
  FROM user_has_book 
  WHERE user_id IN ('Frank', 'Joe')    
  GROUP BY book_id
  HAVING cnt=2)x ON (x.book_id = b.book_id)