Re: full outer join issue?
- From: Michael Moore <michaeljmoore@xxxxxxxxx>
- To: TESTAJ3@xxxxxxxxxxxxxx
- Date: Mon, 26 Apr 2010 14:05:19 -0700
In retrospect I am thinking that what I said was not very clear.
Basically, on the 2nd query, the only way d.department_id can be NULL is if
there was an EMPLOYEE who does not have a department. (assuming of course
that department_id is a PK)
Mike
On Mon, Apr 26, 2010 at 1:58 PM, Michael Moore <michaeljmoore@xxxxxxxxx>wrote:
> The logic of the 2nd outer join is going something like this
>
> FOR EACH EMPLOYEES RECORD
> FIND a departments record matching on (d.department_id =
> e.department_id)
> IF you found a matching departments record then
> create a logical (internal) record that looks like:
> [e.department_id, e.last_name,d.department_id,
> d.department_name]
> ELSE if you did't find a matching departments record
> create a logican (internal record that looks like:
> [e.department_id, e.last_name,d.department_id = NULL,
> d.department_name = NULL]
> END IF
> IF d.department_id is NULL then
> show the output -- e.last_name, d.department_name
> else
> show nothing
> END FOR
>
> Mike
>
>
>
>
> On Mon, Apr 26, 2010 at 1:06 PM, <TESTAJ3@xxxxxxxxxxxxxx> wrote:
>
>>
>> Ok i'm evidently an idiot, i can't seem to get full outer join to work the
>> way I'd expect it to:
>>
>> select e.last_name, d.department_name
>> from departments d full outer join employees e
>> on (e.department_id = d.department_id)
>> where e.department_id is null;
>>
>> select e.last_name, d.department_name
>> from employees e full outer join departments d
>> on (d.department_id = e.department_id)
>> where d.department_id is null;
>>
>>
>> based on the data, the 1st query returns me the one row where an employee
>> doesn't have a dept AND all of the departments that do NOT have employees.
>> so far so good.
>>
>> I've changed the 2nd query every which way possible, fully expecting the
>> same result set, moved the FROM tables back and forth expecting to get the
>> same results, yet all i get with query #2 is the some employee who is not in
>> a dept and NO dept records.
>>
>> 11gr2, on linux, if that matters.
>>
>> if someone wants the data, i'll be glad to export it out and send the .dmp
>> file to ya.
>>
>> thanks, joe
>>
>>
>>
>> _______________________________________
>> Joe Testa, Oracle Certified Professional
>> Senior Engineering & Administration Lead
>> (Work) 614-677-1668
>> (Cell) 614-312-6715
>>
>>
>>
>
Other related posts:
