sql - 在多对多关系中，如何选择集体拥有的记录

Question

我确信我在这里缺少正确的术语，但对于这个示例多对多关系:

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

查看两个用户拥有的所有图书的最佳方式是什么？

现在我只是使用这个:

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'))

我知道这不是最好的方法 - 对吗？我正在使用 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)