3

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

I want to fetch only those authors who have written/publish book just 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'

that give me all authors who have written book on rdbms along with other authors that have also written book on some other subjects so i want to exclude them.

Tunaki
  • 132,869
  • 46
  • 340
  • 423
user1999074
  • 35
  • 4

1 Answers1

1

Try this

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'
Neeraj
  • 8,625
  • 18
  • 60
  • 89