James wrote:

>select m.ID, m."Name", m3."InvNo", m3."InvDate", m2."Price", m."Unit1",
>m."Conversion", m.b1, m."Unit2"
>from "items" m
>left join "invoicedetails" m2 on m.ID = m2."ItemID"
>join "invoice" m3 on m2.ID = m3.ID
>and not exists(select * from "invoice" id3
>join "invoicedetails" id2 on id2.ID = id3.ID
>where m."ID" = id2."ItemID"
>and id3."InvDate" > m3."InvDate")
>where m3."Status" != 'CAN' and m."Type" in ('RAW', 'PLS', 'LBL');
>
>The plan analyzer said ....
>PLAN JOIN (ID3 NATURAL,ID2 INDEX (invoicedetails_IDX1))
>PLAN JOIN (JOIN (M NATURAL,M2 NATURAL),M3 INDEX (RDB$PRIMARY13))
>
>
>

James,

I am trying to understand what your query does....

select
m.ID, m."Name", m3."InvNo", m3."InvDate", m2."Price", m."Unit1",
m."Conversion", m.b1, m."Unit2"
from
"items" m left join
"invoicedetails" m2 on (m.ID = m2."ItemID") join
"invoice" m3 on (m2.ID = m3.ID and not exists(
select
*
from
"invoice" id3 join
"invoicedetails" id2 on (id2.ID = id3.ID)
where
m."ID" = id2."ItemID" and
id3."InvDate" > m3."InvDate")
where
m3."Status" != 'CAN' and
m."Type" in ('RAW', 'PLS', 'LBL');

If you do a item (m) left join invoicedetails (m2), you will get
invoicedetails that has all values with null for the records on invoice
detail that don't have a correlated item, then will do a join on table
invoice (m3), but if the the record on m2 will be null, theren't records
on table invoice (m3) that will be correlated, so I think this left join
is useless.

Did I missed something ?

see you !

--

Alexandre Benson Smith
Development
THOR Software e Comercial Ltda.
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br