MySQL Display All Date between range altough is no record

Question

I Have Table xDateList Contain:

+---------+
 xDateList 
+---------+
2018-11-01
2018-11-02
2018-11-03
2018-11-04
2018-11-05

And Also Table ScanLog

--------------------------------------
ID  Name   ScanDate               Code
--------------------------------------
1   John   2018-11-02 07:00:00    IN
1   John   2018-11-02 10:00:00    OUT
1   John   2018-11-04 08:00:00    IN
1   John   2018-11-04 12:00:00    OUT

I have tried this but it cannot display all record on xDateList, it only show record on table ScanLog

select xDateList.date, 
       scanlog.name, 
       MIN(scanlog.scandate) AS `IN`, 
       MAX(scanlog.scandate) AS `OUT`
from scanlog 
left JOIN xDateList ON xDateList.date = date(scanlog.scandate) 
where scanlog.id='1' 
GROUP BY DATE(scanlog.scandate)

I want result like this

--------------------------------------------
Date         ID   Name   In         Out
--------------------------------------------
2018-11-01   1    John   
2018-11-02   1    John   07:00:00   10:00:00
2018-11-03   1    John
2018-11-04   1    John   08:00:00   12:00:00
2018-11-05   1    John

Thankyou for helping me

Madhur Bhaiya · Accepted Answer · 2018-11-28T09:07:18.643

You need to change the order of tables in the LEFT JOIN. Always remember that in order to consider all the rows from a particular table; that particular table should be the left-most table in the Join.

Also, whenever doing a LEFT JOIN, conditions on the right side table should be specified in the ON clause; otherwise the conditions in WHERE clause can effectively turn it to INNER JOIN instead.

Also, in this case, the GROUP BY should be on the xDateList.date to show all the rows corresponding to xDateList.date values. And, we need to ensure that all non-aggregated columns in the SELECT list are specified in the GROUP BY clause as well. Do check: Error related to only_full_group_by when executing a query in MySql

SELECT xDateList.date, 
       scanlog.name, 
       MIN(scanlog.scandate) AS `IN`,
       MAX(scanlog.scandate) AS `OUT`
FROM xDateList  
LEFT JOIN scanlog  
  ON xDateList.date = date(scanlog.scandate) AND
     scanlog.id='1' 
GROUP BY xDateList.date, scanlog.name

Result

| date       | name | IN                  | OUT                 |
| ---------- | ---- | ------------------- | ------------------- |
| 2018-11-01 |      |                     |                     |
| 2018-11-02 | John | 2018-11-02 07:00:00 | 2018-11-02 10:00:00 |
| 2018-11-03 |      |                     |                     |
| 2018-11-04 | John | 2018-11-04 08:00:00 | 2018-11-04 12:00:00 |
| 2018-11-05 |      |                     |                     |

View on DB Fiddle

MySQL Display All Date between range altough is no record

1 Answers1