I have two tables:
employee
with fields employee_id, firstname, middlename, lastnametimecard
with fields employee_id,time-in,time-out,tc_date_transactionI 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
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.
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.
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.
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.
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);
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With