At 10:17 AM 26/04/2007, you wrote:

> > Not like that! :-)
>
>That's why I asked :-)
>
> > Tip: unless you have to save white space, it is a REALLY good idea
> > to lay out your multi-table statements in a way that makes the
> > linkages obvious. Even if you don't care, you *might* retire some
> > day and bequeath your code to someone else. :-)
>
>totally agree, but I've never seen an join that is easy to read, I
>generally stay with joins in the where clause.
>
>I got it to work, kinda. Here's my SQL:
>
> select Count(*)
> from clientinfo C
> left join PersonInfo PE
> on C.Client_ID = PE.Client_ID
> full join PaycheckInfo Pay on PE.Person_ID = Pay.Person_ID
> where PE.person_type_id = 0
> and Pay.sequence = 1
>
>It returns 2,681 records--and takes 110 seconds to run. If I change it to:
>
> select Count(*)
> from clientinfo C
> left join PersonInfo PE
> on C.Client_ID = PE.Client_ID
> where PE.person_type_id = 0
>
>it returns 2,744 records and only takes 31 ms to run.
>
>Questions:
>
>(1) why doesn't it return the same number of records?

Because they are totally different queries.

>(2) The plan for the first one is
>
> Plan
> PLAN JOIN (PAY NATURAL,JOIN (C NATURAL,PE INDEX (PERSONINFO_CT1)))
>
> Adapted Plan
> PLAN JOIN (PAY NATURAL,JOIN (C NATURAL,PE INDEX (PERSONINFO_CT1)))
>
>What causes it to take so long? Paycheck.Client_Id is a FK,
>(Person_ID; Sequence) is indexed. I added an index to just Sequence
>but that did not speed anything up. What should I look at?

1. Don't use a full join if it is a left join you want.
2. I made a point of telling you to qualify everything. You didn't.

>That is what I need. (I need a field value from the PaycheckInfo
>table for every
>record in ClientInfo, but the record in PersonInfo and in PaycheckInfo
>may not exist)

Your query isn't returning any field values. It's counting
records. What's totally unclear to me is whether you want

a) a count of all the ClientInfo records that have one or more
paycheck records; or
b) a count of all the PaycheckInfo records that have records related
to ClientInfo records; or
c) a count of PaycheckInfo records for each ClientInfo record
(including zero if a ClientInfo record has no correspondence with any
PaycheckInfo records); or
d) Something else entirely.

./heLen