Sql Left Join: Selecting The Last Records In A One-to-many Relationship

I have a customer table, and a detail table. I want to want to pull a record for every customer in the table and show the latest detail data on that customer where applicable. Curr

Solution 1:

You got it almost right.

Your first query removes all customers that don't have details with the specified product, because you didn't specifiy the product filter in the ON condition of the first OUTER JOIN.

SELECT
    cust.Customer
  , cust.Company
  , inv.Date
  , inv.Product
  , inv.Units
  , inv.Extended 
FROM
  customerlist cust
LEFT OUTER JOIN 
  detail inv 
ON 
      cust.customer = inv.customer
  AND inv.Product IN ('CC', 'CG', 'CH')
LEFT OUTER JOIN 
  detail inv2 
ON 
      inv.customer = inv2.customer 
  AND (
          inv.date < inv2.dateOR inv.date = inv2.dateAND inv.customer < inv2.customer
      )
WHERE 
  inv2.customer IS NULL

That should do it.

There is one other thing I think is not quite correct. The AND inv.customer < inv2.customer part should probably be AND inv.id < inv2.id (if there is an id field in the detail table).

That's because the OR condition is filtering the detail records that have the same date by their primary key.

UPDATE

Since the table in question has no primary key field you can use the ROWID ADS feature to solve that:

SELECT
    cust.Customer
  , cust.Company
  , inv.Date
  , inv.Product
  , inv.Units
  , inv.Extended 
FROM
  customerlist cust
LEFT OUTER JOIN 
  detail inv 
ON 
      cust.customer = inv.customer
  AND inv.Product IN ('CC', 'CG', 'CH')
LEFT OUTER JOIN 
  detail inv2 
ON 
      inv.customer = inv2.customer 
  AND (
          inv.date < inv2.dateOR inv.date = inv2.dateAND inv.ROWID < inv2.ROWID
      )
WHERE 
  inv2.customer IS NULL