Clever Geek Handbook

Mysql joins multiple tables excluding some records

I am not very good at MySQL so I need help. I have three tables author (author_id, name), book (book_id, name), publisher (id, author_id, book_id)

I only want to get authors who have written / published the book only on rdbms.

I tried:

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'

which gives me all the authors who wrote the book about rdbms along with other authors who also wrote the book on some other topics, so I want to exclude them.

+3


source to share


1 answer


try it



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'
+1


source






More articles:


All Articles