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

> > One way to make this better is not to mixed implicit and explicit join
> > syntax!!
>
>You're example is fast but don't fit to my needs.
>
> > I can't see the logic here of the outer and wanting to drive it from
> > personlistitems. join on person. Presumably, all employees are
>persons so
> > you have no employees who are not in the person table.
>
>Yes. But there are persons are not employees. A personlist holds
>a set of persons. I need all persons in the list plus their
>employement data (officenr,..), if the person is an employee.
>So I think there is now way around an outer join.

"That does not compute". An outer join will get ALL of the persons,
whether employees or not. An inner join between employees and persons (or
person and employee) will get ONLY employees.

I bet my best bottle of Scotch that an outer join is NOT what you want.

As long as you keep the inner join, this should work just as well, and
produce the same result (or, at least, the one you SAID you wanted!)

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

But, again, the personlistid STILL seems the wrong thing to use to select
members of the the person or employee tables...unless perhaps personlistid
is where you keep only "current persons", or something.

/heLen