SQL GROUP BY/COUNT even if no results

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?

Answers


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.


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.


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


Need Your Help

What is the difference between "compile time" and "run time"?

.net definition

I do not understand what is meant by the terms "compile time" and "run time" (or "runtime").

c++ address string -> long

c++ string long-integer memory-address

I got an adress example: 0x003533 , its a string but to use it i need it to be a LONG but i dont know how to do it :S has anybody a solution?