mysql的左外连接,mysql左外连接

I have two tables:

employee with fields employee_id, firstname, middlename, lastname

timecard with fields employee_id,time-in,time-out,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.

I have a query like this

SELECT *

FROM employee LEFT OUTER JOIN timecard

ON employee.employee_id = timecard.employee_id

WHERE tc_date_transaction = "17/06/2010";

result should like this:

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

解决方案

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";

or write

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);