SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Addict Viral's Avatar
    Join Date
    Nov 2001
    Washington DC
    0 Post(s)
    0 Thread(s)

    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...
    SELECT id, max(time) from TABLE where date = '2004-05-01' and id IN (15,16,18,21) group by id
    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.

    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?

    A computer without Windows is like a chocolate cake without mustard.

  2. #2
    Chessplayer kleineme's Avatar
    Join Date
    Apr 2004
    0 Post(s)
    0 Thread(s)

    you may create a dummy table with just one column (ID) which holds all the possible IDs. Then you can expand your query with a LEFT JOIN between the new dummy table and the old one.
    Never ascribe to malice,
    that which can be explained by incompetence.
    Your code should not look unmaintainable, just be that way.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts