Re: Outer Join

As someone else pointed out, you can't simultaneously constrain a column (L.id between 20 and 22) and use that column as the subservient reference in an outer joun (E.ssn = L.ssn (+). Actually, you can, the the constraint negates the effect of the outer join. To make this work the way you want, try the following:
select E.name, L.item
from EmployeeTable E, LookupTable L
where E.ssn = L.ssn (+)
and E.dept = 8765
and (L.id between 20 and 22 or L.id is null);

That last term in the where clause allows the id to be null in the result.

Jim Wolfe

<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 - 14:31:30 CEST