Click here to Skip to main content
15,749,281 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi all,

I have 2 tables:

1. ppl
ename | edept
ram |	sales
sham |	sales
jadu |	hr
madhu |	hr
sita |	sales
mohan |	sales

2. task
ename | tdate
ram |	01-10-17
jadu |	01-10-17
mohan |	01-10-17

For tdate='01-oct-2017', output should be:
ename |	edept |	tdate
jadu |	hr |	01-10-2017
madhu |	hr |	
mohan |	sales |	01-10-2017
ram |	sales |	01-10-2017
sham |	sales |	
sita |	sales |	

Basically a left join of ppl with task for particular tdate

Here is my query. It works but can it be any simple?

select ppl.ename,ppl.edept,ntbl.tdate from
ppl left join
(select task.ename as ename , ppl.edept, task.tdate as tdate
from ppl,task
where ppl.ename= task.ename 
and task.tdate='01-oct-2017') ntbl
on ppl.ename=ntbl.ename
order by ename;


What I have tried:

select ppl.ename as ename , ppl.edept, task.tdate as tdate
from ppl left join task
on ppl.ename=task.ename
where task.tdate='01-oct-2017';

This does not work as expected and only gives for ename in task.
Posted
Updated 10-Oct-17 5:09am

That is because of the where condition, it restricts the output to the rows which pass the where condition. Remove it, and your query will work. However, you need to reconsider you Db design or your query.

To see if the left join works, write something like

select ppl.ename as ename , ppl.edept, task.tdate as tdate
from ppl left join task
on ppl.ename=task.ename
where ppl.edept='sales';
 
Share this answer
 
Comments
planetz 10-Oct-17 5:47am    
But date is an important filter. I can't leave it out. User will select date and output will be according to that.
Quote:
SQL
select ppl.ename as ename , ppl.edept, task.tdate as tdate
from ppl left join task
on ppl.ename=task.ename
where task.tdate='01-oct-2017';

Your LEFT JOIN returns all the rows from ppl, and any matching rows from task. For ppl rows which don't have a corresponding task row, the result will have Null values for all of the task columns.

Your WHERE clause then filters the result based on the tdate column from task being equal to a specific value. For ppl rows with no task rows, this filter will not be met, because Null is not equal to that value.

You need to move the date filter up to the JOIN:
SQL
SELECT
    ppl.ename, 
    ppl.edept, 
    task.tdate
FROM
    ppl 
    LEFT JOIN task
    ON ppl.ename = task.ename
    AND task.tdate = '01-oct-2017'
;
 
Share this answer
 
Comments
planetz 10-Oct-17 23:44pm    
This is exactly what I want. It worked and query is understandable. Thank you..!!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900