Hi

Svein Erling wrote:

>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?).
>
>
>

Thanks for helping me. :-) 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. 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?
Could using subselects be faster? Can you give me a version of the
script above in sub selects?

Thanks a lot! Greatly appreciate it!

Cheers

James