Re: Outer join query

We're using Oracle not SQL server, besides you misunderstood my question. Thanks anyway.

Patrick Joyal <please.reply_at_to.the.newsgroup> wrote in message news:38a9b033_at_news...
>
> 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