Hmm James, you're trying to a lot with that left join. I know it can
be done using subselects, but let's try using your left join:

select m.ID, m."Name", m3."InvNo", m3."InvDate", m2."Price"
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
where m3."ItemID" = id3."ItemID"
and id3."InvDate" > m3."InvDate")

Originally I thought the latest date was in your invoicedetails table,
and may have messed things up a bit in changing these. Note that the
not exists is in the join. Since all your limiting criteria is on the
right side of a left join this is the only unambiguous place to have
the not exists (if in the where clause, then what should be done for
items which have never been sold?).

HTH,
Set

--- In firebird-support@yahoogroups.com, James <james_027@t...> wrote:
> Iam using FB 1.0.3
>
> Iam having problem constructing this query, wherein I will be using
> three tables. One is item, invoice, and invoicedetails. What I want
> to achieve is that all the items in the item table will get the last
> price information at the invoice and invoicedetails. In invoice
> table is where we could find which is the lastest purchase for a
> particular item and in the invoicedetails table is where we get the
> price.
>
> Iam trying to avoid using SP here/but if I got no choice then I
> think I have to do it in SP.
>
> so far here is what I have.
>
> select m.ID, m."Name", m3."InvNo", m3."InvDate", m2."Price"
> from "items" m left join "invoicedetails" m2 on m.ID = m2."ItemID"
> left join "invoice" m3 on m2.ID = m3.ID
> where exists (select max(d2."InvDate") from "invoicedetails" d
> left join "invoice" d2 on d.ID = d2.id
> where m3.ID = d2.ID and m3."InvDate" = d2."InvDate"
> group by d."ItemID")
>
> but this is all wrong ... this query still giving me the list of all
> items purchase and not the lastest only, which is what I need.
>
> Cheers,
> James