何时使用Where子句和ON子句加入mysql查询？

Question

I have following sql JOIN query:

public function exportAll($date) {
    $sql = "SELECT CONCAT(u.firstname, u.lastname) as fullname, c.early_checkout_remarks, c.delayed_checkin_remarks, DATE(c.checkin_time) as checkin_date, DATE(c.checkout_time) as checkout_date, TIME(c.checkin_time) as checkin_time, TIME(c.checkout_time) as checkout_time 
    FROM users u
    LEFT JOIN checkin_checkout c 
    ON u.id=c.users_id 
    AND (DATE(c.checkin_time) = '%s' OR DATE(c.checkout_time) = '%s') 
    AND u.deleted IS NULL
    ORDER BY fullname,checkin_date";
    $records = $this->db->exec(sprintf($sql,$date,$date));
    return $records;

I only want records of users who are not deleted. But this query is also returning users with values in 'deleted' column.

Answer 1

For that I think you need to move

AND u.deleted IS NULL 

into a WHERE clause. Currently that AND is applied to the join only, not to the final result. And since it's a LEFT join, it doesn't fully restrict the results.

Try:

SELECT 
  CONCAT(u.firstname, u.lastname) as fullname, 
  c.early_checkout_remarks, 
  c.delayed_checkin_remarks, 
  DATE(c.checkin_time) as checkin_date, 
  DATE(c.checkout_time) as checkout_date, 
  TIME(c.checkin_time) as checkin_time, 
  TIME(c.checkout_time) as checkout_time 
FROM 
  users u
  LEFT JOIN 
    checkin_checkout c 
      ON u.id = c.users_id 
      AND (DATE(c.checkin_time) = '%s' OR DATE(c.checkout_time) = '%s') 
WHERE 
  u.deleted IS NULL
ORDER BY 
  fullname,
  checkin_date

It's not clear from your question where the part about check-in dates should also be in the where clause. We'd need to know more about the requirements and your data structure. But it's something you can try if you need to.

Note also I reformatted your query - I (personally) find queries formatted like this can be read and understood a lot faster and more accurately. It's easier to see what is being done where, and what applies to what.