Edit Question - KnowledgeHub

Title Body

Here's my simple SQL question... I have two tables: Books ------------------------------------------------------- | book_id | author | genre | price | publication_date | ------------------------------------------------------- Orders ------------------------------------ | order_id | customer_id | book_id | ------------------------------------ I'd like to create a query that returns: -------------------------------------------------------------------------- | book_id | author | genre | price | publication_date | number_of_orders | -------------------------------------------------------------------------- In other words, return every column for ALL rows in the Books table, along with a calculated column named 'number_of_orders' that counts the number of times each book appears in the Orders table. (If a book does not occur in the orders table, the book should be listed in the result set, but "number_of_orders" should be zero . So far, I've come up with this: SELECT books.book_id, books.author, books.genre, books.price, books.publication_date, count(*) as number_of_orders from books left join orders on (books.book_id = orders.book_id) group by books.book_id, books.author, books.genre, books.price, books.publication_date That's almost right, but not quite, because "number_of_orders" will be 1 even if a book is never listed in the Orders table. Moreover, given my lack of knowledge of SQL, I'm sure this query is very inefficient. What's the right way to write this query? (For what it's worth, this needs to work on MySQL, so I can't use any other vendor-specific features).

Tags (comma-separated) Cancel