Qualified Joins

I need a query that compares two tables with times in them and returns only
the common records with the lowest time.
Table1
recordID ArrivalTime
12345 12:01am
12346 12:30am
12347 12:45am
Table2
recordID ArrivalTime
12345 12:03am
12346 12:29am
12347 12:44am
The result should be
recordID ArrivalTime
12345 12:01am
12346 12:29am
12347 12:44am
Can someone point me in the right direction please?Hi Dave,
SELECT recordID,MIN(ArrivalTime)
FROM
(
SELECT recordID,ArrivalTime
FROM TABLE1
UNION
SELECT recordID,ArrivalTime
FROM TABLE2
) SubQuery
GROUP BY recordID
--OR
SELECT
recordID ,
CASE WHEN T1.ArrivalTime < T2.ArrivalTime THEN T1.ArrivalTime
ELSE T2.ArrivalTime END AS ArrivalTime
FROM TABLE1 T1
FULL OUTER JOIN TABLE2 T2
ON T1.recordid = T2.recordid
HTH, jens Suessmeyer.|||The 2nd query doesn't handle nulls...
SELECT
COALESCE( T1.recordid , T2.recordid ) AS recordid
, (CASE
WHEN T2.ArrivalTime IS NULL OR T1.ArrivalTime < T2.ArrivalTime ) THEN
T1.ArrivalTime
ELSE T2.ArrivalTime
END) AS ArrivalTime
FROM TABLE1 T1
FULL OUTER JOIN TABLE2 T2
ON T1.recordid = T2.recordid
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1131981403.240416.108000@.g47g2000cwa.googlegroups.com...
> Hi Dave,
>
> SELECT recordID,MIN(ArrivalTime)
> FROM
> (
> SELECT recordID,ArrivalTime
> FROM TABLE1
> UNION
> SELECT recordID,ArrivalTime
> FROM TABLE2
> ) SubQuery
> GROUP BY recordID
>
> --OR
> SELECT
> recordID ,
> CASE WHEN T1.ArrivalTime < T2.ArrivalTime THEN T1.ArrivalTime
> ELSE T2.ArrivalTime END AS ArrivalTime
> FROM TABLE1 T1
> FULL OUTER JOIN TABLE2 T2
> ON T1.recordid = T2.recordid
>
> HTH, jens Suessmeyer.
>