0

Previous Solution for one Value

Is there a way to return multiple NULL or 'nan' values if we have a query with multiple ID values? In other words, if a table has a datetime "ID" which is a primary key. It is incrementing in ticks of one second. Some of those seconds (entries) are missing and you would want to return 'nan' or 'empty' once you query a large range of IDs.

Working Code for one value:

SELECT ID FROM TABLE WHERE ID in ('2017-06-29 23:10:00') UNION ALL (SELECT 'nan') LIMIT 1

Trying to achieve dynamic query for multiple values. In this example, I query 3 IDs, but the list can grow to thousands - non-working Example:

SELECT ID FROM TABLE WHERE ID in ('2017-06-29 23:10:00', '2017-06-29 23:10:01','2017-06-30 00:11:03') UNION ALL (SELECT 'nan') LIMIT 1
user7769718
  • 41
  • 5
  • 1
    If I understand correctly, you have a [SQL "gaps-and-islands"](https://stackoverflow.com/questions/tagged/gaps-and-islands+sql) problem. Many questions here deal with "missing days" in data series. Your problem is essentially the same, except that you have missing seconds. – pilcrow Jun 29 '17 at 16:10
  • That's correct. – user7769718 Jun 29 '17 at 16:27
  • Can you show the table contents and the desired results? – Barmar Jun 29 '17 at 17:12

1 Answers1

1

Use a UNION to synthesize a table with all the IDs you want to match, then left join this with the real table. When there's no match in the table, LEFT JOIN results in NULL for its columns, and you can test for this and show the default value.

SELECT IFNULL(t2.id, 'nan') AS id
FROM (SELECT '2017-06-29 23:10:00' as id
      UNION ALL
      SELECT '2017-06-29 23:10:01'
      UNION ALL
      SELECT '2017-06-29 23:10:02') AS t1
LEFT JOIN TABLE AS t2 ON t1.id = t2.id

See What is the most straightforward way to pad empty dates in sql results (on either mysql or perl end)? for how you can automate the process of creating the table with all the timestamps.

Arulkumar
  • 12,966
  • 14
  • 47
  • 68
Barmar
  • 741,623
  • 53
  • 500
  • 612