I'm fairly new to mySQL and T-SQL in general and am looking for what I
thought would be a fairly straightforward query. Here are some sample
tables and data.
Let's say I have a table of authors and a table of books. I want to
bring back a result set with a row for each author showing the total
number of books they have of a particular genre in the books table. If
an author has no books in the books table of that genre they would
have row in the resultset with a zero for their number of books.
Here is some sample data. The resultset I'm looking for is at the
bottom. In this example I'm looking for the number of books of the
scifi genre each author has in this table.
I assumed it would be a fairly straightforward left outer join but
when I add the "WHERE b.genre = 'scifi' " it removes the authors from
the result set with no books of that particular genre.
Please reply to the newsgroup.
Authors
----------------------------------
a_id a_name a_publishing_co
----------------------------------
1 fred McMillon
2 sally BooksRUs
3 george Streetscape
Books
-------------------------------
b_id a_id b_genre
-------------------------------
1 1 scifi
2 1 history
3 1 scifi
4 2 scifi
resultset I'd like
----------------------
totals a_id a_name
----------------------
2 1 fred
1 2 sally
0 3 george
The following queries are what I have attempted but they both bring
back incorrect data (ie, no row for george)
SELECT IF(b.a_id IS NULL,0,count(1)) as 'totals', a.a_name,
a_publishing_co
FROM authors a LEFT OUTER JOIN books b ON b.a_id = a.a_id
WHERE b.b_genre = 'scifi'
GROUP BY b.a_id;
SELECT count(1) as 'totals', a.a_name, a_publishing_co
FROM authors a LEFT OUTER JOIN books b ON b.a_id = a.a_id
WHERE b.b_genre = 'scifi'
GROUP BY b.a_id;