Re: Outer Join

I'll assume "and and" is a typo in your example. I'm no expert so take this with a grain of salt.

First, why it does not work ...
You are saying 3 things must be true, the order that you code these 3 things does not matter, so I will re-order them because this should make your logic error more clear to you ...

1) department must equal 8765
2) L.id must be between 20 and 22
3) an E.ssn row must be successfully matched with an L.ssn row

    and oh, by the way, if there is no match, plug in NULL values     for the L.ssn row and pretend we got a match. The NULL stuff     is what the (+) does.

Now at run-time 3 , the JOIN is done first. If the join results in a NULL value for L.id, can we say that item 2 is true? No. So you will never get anything back but rows where 1 and 2 are both true.

I don't know how one could do what you want in a single select statement, but you could create a VIEW like
v_abc is
select ssn,id,item from lookup table
where id between 20 and 22

then do
Select name, item
from employeetable,v_abc(+)
where department =8765;

I think this would work.
Mike

<dennishancy_at_eaton.com> wrote in message news:9a2kar$iuf$1_at_news.netmar.com...
> I have two tables: 1) Employee Table, and 2) Lookup Table.
>
> The primary key of the Employee table is SSN, and is also a foreign key
> within
> the Lookup table.
>
> The primary key of the Lookup table is an ID number, indicating the type
 of
> information being looked up.
>
> I'm interested in producing a list of all employees within a certain
> department
> along with certain items in this lookup table.
>
> For instance, assume that the items in the lookup table I am interested
 have
> ID
> numbers of 20, 21, and 22, and the department number is 8765. Here's the
> SQL statement I thought I would need.
>
> select E.name,
> L.item
> from EmployeeTable E,
> LookupTable L
> where E.ssn = L.ssn(+)
> and and L.id between 20 and 22
> and E.dept = 8765;
>
>
> When I run this, it only gives me a list of those employees who have
 lookup
> items of 20, 21, and 22. It does not include those employees who do not
> have these items.
>
> Do I need to add something else to my outer join? Thanks.
>
>
> Dennis Hancy
> Eaton Corporation
> Cleveland, OH
>
> ----- Posted via NewsOne.Net: Free (anonymous) Usenet News via the
 eb -----
> http://newsone.net/ -- Free reading and anonymous posting to 60,000+
 groups
> NewsOne.Net prohibits users from posting spam. If this or other posts
> made through NewsOne.Net violate posting guidelines, email
abuse_at_newsone.net Received on Mon Apr 02 2001 - 04:15:49 CEST