Outer join query

From: Dana Jian <djian_at_trasa.com>
Date: 2000/02/15
Message-ID: <38a9ab28$0$1401@news.choice.net>#1/1

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