Question 175957 in Mysql Question posted in
The official MySQL documentation can be found here.

Does MySQL return an empty row if no record matches in a single table?

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.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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;

  2. 0

    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;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search