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

>--- In firebird-support@yahoogroups.com, "Ann W. Harrison"
><aharrison@i...> wrote:
> >> Francois aka broesel2003 wrote:
>
>Thanks for your nice answer! I understand it better, but the problem
>itself isn't solved.
>
>
>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. A person may be a
>employee. If it's a employee i want his office number.
>Like the last example, he does a full table scan (600ms). But the
>bird should know, I only need the five persons in my personlist.
>Without the outer join on employees, bird does the job perfecly
>in 50ms on 10'000 persons. I can't find any way to make this better.

One way to make this better is not to mixed implicit and explicit join
syntax!!

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. An inner join from
employee to person will get person data only on persons who are employees
and the (rather costly) join to pl. Why? Don't you have any other way to
target an individual person except through personlistid?

I don't see any rationale for an outer join. How many lastnames and
officenrs does each employee Have?

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

/heLen