> Thanks for helping me. :-)

You're welcome, puzzles are fun!

> Iam getting an error with the script above because I dont have the
> "ItemID" field in my invoice table but rather its on invoicedetails
> table.

sorry,

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
join "invoicedetails" id2 on id2.ID = id3.ID
where m3."ItemID" = id2."ItemID"
and id3."InvDate" > m3."InvDate")

> With your scripts I learn a new thing. the exists could be
> use in join clauses I always thought that exists is use only in
> where clause. One more thing you've mention Iam trying to do a lot
> with my left join ... is there a problem with that?

Basically there are two "problems".

1) I rarely use outer joins, so it is more difficult for me to help
you ;.)

2) The only place to limit the rows on the right side of the left join
is in the join clause. If included in the where clause, it may be
considered ambiguous in that left join says that all rows should be
included whether or not any rows on the right side matches, whereas
things put in the where clause do limit which rows are returned.

> Could using subselects be faster?

In this case, I doubt it.

> Can you give me a version of the script above in sub selects?

Well, I can try, but haven't time to check that what I write is
actually correct:

select m.ID, m."Name",
(select m3."InvNo" from "invoicedetails" m2
join "invoice" m3 on m2.ID = m3.ID
where m.ID = m2."ItemID"
and not exists(select * from "invoice" id3
join "invoicedetails" id2 on id2.ID = id3.ID
where m3."ItemID" = id2."ItemID"
and id3."InvDate" > m3."InvDate")) as "InvNo",
(select m3."InvDate" from "invoicedetails" m2
join "invoice" m3 on m2.ID = m3.ID
where m.ID = m2."ItemID"
and not exists(select * from "invoice" id3
join "invoicedetails" id2 on id2.ID = id3.ID
where m3."ItemID" = id2."ItemID"
and id3."InvDate" > m3."InvDate")) as "InvDate",
(select m2."Price" from "invoicedetails" m2
join "invoice" m3 on m2.ID = m3.ID
where m.ID = m2."ItemID"
and not exists(select * from "invoice" id3
join "invoicedetails" id2 on id2.ID = id3.ID
where m3."ItemID" = id2."ItemID"
and id3."InvDate" > m3."InvDate")) as "Price"
from "items" m

HTH,
Set