Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

mysql left outer join

Tags:

mysql

I have two tables:

  1. employee with fields employee_id, firstname, middlename, lastname
  2. 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
like image 468
tirso Avatar asked Jun 17 '10 04:06

tirso


People also ask

IS LEFT join same as left outer join?

There really is no difference between a LEFT JOIN and a LEFT OUTER JOIN. Both versions of the syntax will produce the exact same result in PL/SQL. Some people do recommend including outer in a LEFT JOIN clause so it's clear that you're creating an outer join, but that's entirely optional.

What does (+) mean in SQL join?

The plus sign is Oracle syntax for an outer join. There isn't a minus operator for joins. An outer join means return all rows from one table. Also return the rows from the outer joined where there's a match on the join key. If there's no matching row, return null.

What is left join and outer join?

LEFT (OUTER) JOIN : Returns all records from the left table, and the matched records from the right table. RIGHT (OUTER) JOIN : Returns all records from the right table, and the matched records from the left table. FULL (OUTER) JOIN : Returns all records when there is a match in either left or right table.

Does MySQL have left join?

The Left Join in MySQL is used to query records from multiple tables. This clause is similar to the Inner Join clause that can be used with a SELECT statement immediately after the FROM keyword.


1 Answers

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);
like image 189
Loxley Avatar answered Nov 15 '22 20:11

Loxley