How to deal with missing values in IN clause
I need to select the max value for the time field in a table for a given range of values in the id field like so...
Now assuming they all have values for that day, this works, but if there was no value for, say, id 18, it has no entry whatsoever (as expected). But I need it to show me something. I've tried a count(*), still nothing, and even an IFNULL(max(time),999) , but obviously this doesn't work either since there's not a null value, there's simply no value for that id within the IN clause.
SELECT id, max(time) from TABLE where date = '2004-05-01' and id IN (15,16,18,21) group by id
This is an incredibly simplified version of my query (there are actually 1000s of ids within many tables and doing some math to subtract the current time from the last update time) so breaking these into individual queries would really slow things down too much.
Any idea how I can force it to return a known/static/null/any value for every id regardless of whether it matches a row or not?