Making oracle left join query simple

Question

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 9-Oct-17 21:53pm

planetz

Updated 10-Oct-17 5:09am

Add a Solution

2 solutions

Solution 1

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

Posted 9-Oct-17 23:01pm

GKP1992

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.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Richard Deeming · Accepted Answer · 2017-10-10T05:07:00

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