JOIN ONTO SINGLE RECORD 1

[Smeat]

Hi All

Is there any way I can have an outer join that only joins onto a single record.

I.e.

I am selecting data from my customer table but also want to include data from their last order.

so

Select C.Title, C.FirstName, C.LastName, O.OrderDate AS LastOrderDate, O.OrderTotal AS LastOrderTotal
FROM
Customer C
   LEFT OUTER JOIN
Order O
   ON
C.[ID] = O.CustomerID

If this cannot be done, what would be the best approach?

I thought about using UDF's but that just seems far too inefficient as I would have to call one UDF for each field I wanted from the Order table.

TIA

Smeat

 

[r937]

SELECT ...
  FROM Customer C
LEFT OUTER 
  JOIN [Order] O
    ON O.CustomerID = C.ID
   AND O.OrderDate =
       ( select max(OrderDate)
           from [Order]
          where CustomerID = C.ID )

r937.com | rudy.ca

 

[Smeat]

Have a start for such a great response.

Thanks loads, I just spent over an hour on msdn trying to work out the best approach, your solution took 2 minutes to implement.

Smeat