Re: Outer join query

Select Objects.Object_ID, Objects.Object_name, Services.Service_Date From
  Objects Left Join Services
 ON Objects.Objects_Id = Services.Objects_ID

Dana Jian wrote in message <38a9ab28$0$1401_at_news.choice.net>...
>Hi,
>
>There're two tables:
>Objects: Object_ID is unique
>
>Object_ID Object_Name
>1 AAA
>2 BBB
>3 CCC
>
>Services: Objects_ID is not unique, and NOT all objects appear in this
 table
>Object_ID Service_Date
>2 01/01/2000
>2 12/30/1999
>
>The query need to list all object name in the objects table and most recent
>service_date,
>Object_ID Object_Name Service_Date
>1 AAA
>2 BBB 01/01/2000
>3 CCC
>
>I was trying to use this query:
>
>SELECT objects.object_ID, objects.object_name,
> Services.service_date
>FROM Objects, Services
>WHERE Objects.Object_ID = Services.Object_ID (+) AND
> Services.service_date =
> (
> SELECT MAX(Services.service_date)
> FROM Services, Objects Objects2
> WHERE Services.Object_ID = Objects2.Object_ID
> AND Objects2.Object_ID = Objects.Object_ID
> )
>ORDER BY objects.object_ID
>
>It's working fine in SQL server (except different outer join syntax), but
 in
>Oracle the above query just return objects appear in the service table(only
>BBB object is returned).
>
>Any ideas?
>
>Thanks in advance for any help.
>
>Dana
>djian_at_trasa.com
>
>
>
Received on Tue Feb 15 2000 - 00:00:00 CST