Re: Simple Query Question?

In <DLI6vD.F7y.0.-s_at_cs.vu.nl> jalten_at_cs.vu.nl (Alten JP) writes:
>
>Hi, I think I have a simple question....
>
>If I have a table with information on people, including the
>fields number, name and supervisor_number
>What if I want the names of all people, and if they have
>a supervisor (supervisor_number is not zero) then I want the name
>of the supervisor too.
>I can try this:
>select p.name, s.name where p.supervisor_number=s.number
>but that would not give me all people....
>
>trying something like this:
>select p.name, s.name
>where p.supervisor_number=s.number or p.supervisor_number=0
>returns an error.
>
>Any suggestions?
>It would be nice if the query is not nested...
>
>Thanks,
>
>Jelle Paul
>
>
>--
>+-------------------------------------------------------+
>| Jelle Paul Alten | jalten_at_cs.vu.nl |
>| Vrije Universiteit Amsterdam | |
>+-------------------------------------------------------+

You need to do an outer join.

For example

select p.name, NVL(s.name,'Unknown') /* The NVL is optional */ from table1 p, table1 s
where p.supervisor_number=s.number(+)

Eric Received on Mon Jan 22 1996 - 00:00:00 CET