SQL Left join: selecting the last records in a one-to-many relationship

Question

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.

Currently my where clause is filtering out customers.

I have tried moving the where clause into the left outer join, but I have not been able to get the desired result.

It does not seem to filter the product at all when I run the query.

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
LEFT OUTER JOIN 
  detail inv2 
ON 
      inv.customer = inv2.customer 
  AND (
          inv.date < inv2.date 
       OR inv.date = inv2.date AND inv.customer < inv2.customer
      )
WHERE 
      (
          inv.Product = 'CC' 
       OR inv.Product = 'CG' 
       OR inv.Product = 'CH'
      ) 
  AND inv2.customer IS NULL

My question is similar to

SQL join: selecting the last records in a one-to-many relationship

I'm trying for the same thing just want to include every customer and filter by product.

Update

Sample data

Here is my Original Query, which is great except for I am missing customers

If I remove the where clause and insert it into the left join as follows

LEFT OUTER JOIN 
  detail inv2 
ON 
      inv.customer = inv2.customer 
  AND (
           inv.date < inv2.date 
       OR  inv.date = inv2.date AND inv.customer < inv2.customer
      ) 
  AND (    
           inv.Product = 'CC' 
       OR  inv.Product = 'CHECK' 
       OR  inv.Product = 'ACH'
      )

Here is the result There are product columns showing up that are not 'CC' etc.. And the customers are duplicated.

Answer 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.date 
       OR inv.date = inv2.date AND 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.date 
       OR inv.date = inv2.date AND inv.ROWID < inv2.ROWID
      )
WHERE 
  inv2.customer IS NULL