mysql左外连接 [英] mysql left outer join

问题描述

我有两个表:

1. employee，其字段为employee_id，名字，中间名，姓氏
2. timecard，其字段为employee_id，time-in，time-out，tc_date_transaction

1. employee with fields employee_id, firstname, middlename, lastname
2. timecard with fields employee_id,time-in,time-out,tc_date_transaction

我想选择所有具有相同time_card的employee_id的员工记录，并且日期等于当前日期.如果没有与当前日期相等的记录，那么即使没有time-in，timeout和tc_date_transaction，也会返回员工的记录.

I want to select all employee records which have the same employee_id with timecard and date is equal with the current date. If there are no records equal with the current date then return also the records of employee even without time-in,timeout and tc_date_transaction.

我有这样的查询

SELECT * 
  FROM employee LEFT OUTER JOIN timecard 
       ON employee.employee_id = timecard.employee_id
 WHERE tc_date_transaction = "17/06/2010";

结果应如下所示:

employee_id | firstname | middlename | lastname | time-in | time-out | tc_date_transaction
------------------------------------------------------------------------------------------
     1      | john      | t          | cruz     | 08:00   | 05:00    | 17/06/2010     
     2      | mary      | j          | von      | null    | null     | null

推荐答案

您正在过滤tc_date_transaction，该函数将过滤该字段中的所有空值，甚至包括外部联接生成的空值，因此无法达到其目的.将过滤器"tc_date_transaction ="17/06/2010"移到join子句中，它将起作用.

You are filtering tc_date_transaction which filters all null values in this field, even those generated by the outer-join and therefore defeats its purpose. Move the filter "tc_date_transaction = "17/06/2010"" into the join clause and it will work.

SELECT * 
  FROM employee LEFT OUTER JOIN timecard 
       ON employee.employee_id = timecard.employee_id and tc_date_transaction = "17/06/2010";

或写

SELECT * 
  FROM employee LEFT OUTER JOIN timecard 
       ON employee.employee_id = timecard.employee_id 
  where (tc_date_transaction = "17/06/2010" or tc_date_transaction is null);

这篇关于mysql左外连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！