Get empty row if no record matches mysql in a single table

Question

CREATE TABLE table
 (`id` int, `date` date, `time` time);
INSERT INTO table
  (`id`, `date`, `time`)
 VALUES  
 (1, '2022-05-22', 14:00:00),
 (2, '2022-05-23', 07:35:00),
 (4, '2022-05-23', 14:00:00);

Expected Output:

date	time
2022-05-22	14:00:00
2022-05-22	NULL
2022-05-23	07:35:00
2022-05-23	14:00:00

As you notice, there's no entry for 07:35 at date 2022-05-22.

I have tried join on single table and CTE also but nothing works.

I'm using PHP with MySQL

Thanks in advance.

user can choose any date for any time, we are not asking for null record. — Shravan Sharma, May 26 '23 at 08:50
Update your post with your actual data and query. Attempting to help in semi-blind mode may result in a waste of time for both sides (you and who's helping). — lemon, May 26 '23 at 09:26

score 2 · Accepted Answer · answered May 26 '23 at 08:59

2

A calendar table approach might be what you are looking for. Consider the following select query which however generates the exact output you want.

WITH dates AS (
    SELECT '2022-05-22' AS dt UNION ALL
    SELECT '2022-05-23'
),
times AS (
    SELECT '07:35:00' AS tm UNION ALL
    SELECT '14:00:00'
)

SELECT d.dt AS date, yt.time
FROM dates d
CROSS JOIN times t
LEFT JOIN yourTable yt
    ON yt.date = d.dt AND
       yt.time = t.tm
ORDER BY d.dt, t.tm;

answered May 26 '23 at 08:59

Tim Biegeleisen

502,043
27
286
360

getting null for all time, https://pasteboard.co/x951iOBC642L.png – Shravan Sharma May 26 '23 at 09:07
1

...then you're not running my code, or else `yourTable` is not what you actually have defined above. – Tim Biegeleisen May 26 '23 at 09:11
yes, you are right, I'm trying to filter the result using where and that created the problem. I have a question regarding hardcoded date and time, can we get rid of hardcoded date and time or at least time? – Shravan Sharma May 26 '23 at 09:23
Read: https://stackoverflow.com/questions/10132024/how-to-populate-a-table-with-a-range-of-dates – Tim Biegeleisen May 26 '23 at 09:31

score 0 · Answer 2 · answered May 26 '23 at 12:34

Solution given by @Tim Biegeleisen is working just fine but unfortunately my server doesn't suppoert CTE, so who has little knowledge of CTE or have lower version of mysql can try this solution...

SELECT DISTINCT(d.dt) AS date, yt.time
FROM (SELECT date as dt FROM tableName ) as d 
CROSS JOIN (SELECT time as tm FROM tableName ) as t
LEFT JOIN tableName yt
ON yt.date = d.dt AND yt.time = t.tm
ORDER BY d.dt, t.tm;

Get empty row if no record matches mysql in a single table

2 Answers2