At 06:18 AM 18/04/2004 +0000, you wrote:

>--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@t...>
>wrote:
>
> > "That does not compute". An outer join will get ALL of the persons,
> > whether employees or not.
>
>This is exactly my goal! I want all persons and if the perons is an
>employee i want additional infos from the employee table.

Let's work this out.

All employees are persons, yes?

Not all persons are employees, right?

A personlist could contain both employees and other persons (customers,
lovers, family...) Let's say you answer "yes", then you are back to Ann's
suggestion ---AS LONG AS you avoid those implicit joins. They are dead
meat. This is the 21st century.

>User should be able to put some persons into a personlist for his
>purpose. I.e. his team members or something.

OK, and you want this user to select all the persons from his personlist
and find the person details and (if present) the employee details.

OK.

select p.lastname, e.officenr
from person
LEFT join employee e
on p.personid = e.personid
join personlistitems pl
on pl.personid = p.personid
personlistitems pl,
where
pl.personlistid =122;

That's all you need. You will get every person on the personlistitems that
is in the list having a pl id of 122. If any of those persons are
employees, you will also get the office numbers.

If you take out the pl id, you will get all the persons in the person
table, with the extra bits if present.

/hb