Re: HELP! Outer joins

you also need to put a (+) outer join on the service_date(+) = (subquery....)

Wayne Linton

Dana Jian wrote:

> 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

--
Wayne Linton
ORA*DBA Systems Consulting Ltd.
ora-dba_at_home.com