CODEWITHSUNDEEP
sqlmysql
Tim Lytle
Tim Lytle

Reputation: 17624

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

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

Upvotes: 0

Views: 43

Answers (1)

a1ex07
a1ex07

Reputation: 37364

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)

Upvotes: 1

Related Questions