Re: HELP! Outer joins

>
>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 would try ...

SELECT objects.object_ID, objects.object_name,

      max(Services.service_date)
FROM Objects, Services
WHERE Objects.Object_ID = Services.Object_ID (+) group by objects.object_ID, objects.object_name

The services.service_date column would be null in rows that had no matching Services data; you can do a MAX on NULLs, and get the max on the not null values.

Hope that helps
Dan Hekimian-Willams Received on Wed Feb 16 2000 - 00:00:00 CET