I have three tables: Book (Id) , Topic (Id) , BookTopic (BookId, TopicId) . There is a query that for each Topic returns the number of Book in this Topic:

SELECT T.Id, T.TopicName, count(BT.TopicId) AS 'BooksCount' FROM Topic T, BookTopic BT WHERE T.Id = BT.TopicId GROUP BY T.Id, T.TopicName

But there is a problem: if no book is associated with a topic, then such a topic is not returned. And I wanted such a topic to return with a value of BooksCount = 0. How can I do that?

    1 answer 1

    To do this, use the LEFT JOIN

     SELECT T.Id, T.TopicName, count(BT.TopicId) AS 'BooksCount' FROM Topic T LEFT JOIN BookTopic BT ON T.Id = BT.TopicId GROUP BY T.Id, T.TopicName