Btukfyl

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

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