Mysql Join 多表排除部分记录

Question

我的 MySQL 不是很好，所以需要一些帮助。我有三个表 author (author_id, name), book (book_id, name), publisher(id, author_id, book_id)

我只想获取那些仅在 rdbms 上撰写/出版书籍的作者。

我试过:

SELECT a.author_id, a.name, b.name as bookname
FROM author a
LEFT JOIN publisher p ON p.author_id = a.author_id
INNER JOIN book b ON b.book_id = p.book_id ANd b.name='rdbms'

这给了我写过关于 rdbms 的书的所有作者以及也写过关于其他主题的书的其他作者，所以我想排除他们。

Answer 1

试试这个

SELECT a.author_id, a.name, b.name as bookname
FROM author a
LEFT JOIN publisher p ON p.author_id = a.author_id
INNER JOIN book b ON b.book_id = p.book_id
group by a.author_id
HAVING count(p.book_id) = 1 AND bookname='rdbms'