SQL GROUP BY/COUNT even if no results

Question

I am attempting to get the information from one table (games) and count the entries in another table (tickets) that correspond to each entry in the first. I want each entry in the first table to be returned even if there aren't any entries in the second. My query is as follows:

SELECT g.*, count(*) 
FROM games g, tickets t 
WHERE (t.game_number = g.game_number
   OR NOT EXISTS (SELECT * FROM tickets t2 WHERE t2.game_number=g.game_number))
GROUP BY t.game_number;

What am I doing wrong?

Answer 1

You need to do a left-join:

SELECT g.Game_Number, g.PutColumnsHere, count(t.Game_Number) 
FROM games g
LEFT JOIN tickets t ON g.Game_Number = t.Game_Number
GROUP BY g.Game_Number, g.PutColumnsHere

Alternatively, I think this is a little clearer with a correlated subquery:

SELECT g.Game_Number, G.PutColumnsHere,
  (SELECT COUNT(*) FROM Tickets T WHERE t.Game_Number = g.Game_Number) Tickets_Count
FROM Games g

Just make sure you check the query plan to confirm that the optimizer interprets this well.

Answer 2

"FROM games g, tickets t " is the problem line. This performs an inner join. Any where clause can't add on to this. I think you want a LEFT OUTER JOIN.

Answer 3

You need to learn more about how to use joins in SQL:

SELECT g.*, count(*) 
FROM games g
 LEFT OUTER JOIN tickets t 
 USING (game_number)
GROUP BY g.game_number;

Note that unlike some database brands, MySQL permits you to list many columns in the select-list even if you only GROUP BY their primary key. As long as the columns in your select-list are functionally dependent on the GROUP BY column, the result is unambiguous.

Other brands of database (Microsoft, Firebird, etc.) give you an error if you list any columns in the select-list without including them in GROUP BY or in an aggregate function.