At 12:47 AM 4/18/2004, Francois Zbinden wrote:

>It's a little problem :) My example had a mistake, of course I
>like to get some information from the employee table.
>Here is a better example:
>
>select p.lastname, e.officenr
>from
>personlistitems pl,
>person p left outer join
>employee e on (p.personid = e.personid)
>where
>pl.personid=p.personid and
>pl.personlistid =122;
>
>I select a personlist witch contains 5 persons.

Ah! The lack of reference to employees was a red herring. The missing clue
was actually the third table. Helen's right. Your problem comes from
mixing SQL-89 and SQL-92 join syntax. You've even shaken out the piece of
cerebral plaque that blocked my last attempt to remember the precedence
rules.

The rules say that on conditions are evaluated before where
conditions, which has the effect that SQL-92 joins, aka explicit joins,
syntax:
<table> [join type] join <table> on <condition> )
are higher precedence than SQL-89 joins, aka implicit joins, syntax:
<table>, <table>[, ...] where <condition>

Here's your statement formatted to emphasize precedence

select p.lastname, e.officenr
from personlistitems pl,
person p left outer join employee e on (p.personid = e.personid)
where pl.personid = p.personid
and pl.personlistid = 122

The whole of the third line must be evaluated before the result can be
joined with the personlistitems. In your particular case, that doesn't
matter, but one can imagine cases where the effect of the left outer join
would affect [english is a wonderful language - "the effect affects" means
something totally different from "the affect effects". wierd.] the
implicit join.

So, you've got a choice of performing a large nasty outer join once or once
for each member of the selected personlist.

Try this instead.

select p.lastname, e.officenr
from personlistitems pl,
join person p on (pl.personid = p.personid)
left outer join employee e on (p.personid = e.personid)
where pl.personlistid = 122

It should get the right answer and be reasonably quick.

Cheers,


Ann