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).