Keep all records in "WHERE IN()" clause, even if they are not found
mysql
Solution
Here's an alternative to Micheal's solution (not a bad solution, mind you -- even with "a lot" of ID's), so long as you're not querying against a cluster.
create temporary table __ids (
id int unsigned primary key
) engine=MEMORY;
insert into __ids (id) values
(1),
(2),
(3)
;
SELECT table.id, sum(views) as total_views
FROM __ids left join table using (id)
GROUP BY table.id
ORDER BY total_views ASC
And if your query becomes complex, I could even conceive of it running more efficiently this way. But, if I were you, I'd benchmark this option with Michael's ad-hoc UNION'ed table option using real data.
Problem
I have the following mysql query: ``` SELECT id, sum(views) as total_views FROM table WHERE id IN (1,2,3) GROUP BY id ORDER BY total_views ASC ``` If only `id 1,3` are found in the database, i still want `id 2` to appear, with `total_views` being set to 0. Is there any way to do that? This cannot use any other table.