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

Here is my original query

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.

enter image description here

Does my update help with sample data? If there is anything I can do to make it more clear let me know. Appreciate the help! — Trevor, Jun 12 '13 at 22:29
Have you tried to add the product filtering code to the first `OUTER JOIN` instead of the second one? — Jens Mühlenhoff, Jun 13 '13 at 09:36
Regarding "the latest detail per customer". In the detail table what if you have 5 items with same date... Do you want all for that date, or just the very last one... and if so, what is the auto-increment column on that detail table. — DRapp, Jun 13 '13 at 13:22

Jens Mühlenhoff · Accepted Answer · 2013-06-13T15:59:43.200

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

Good observation, unfortunately the detail table does not have a id field. But luckily the cases are rare when there would be two entries of the specified product for a customer on the same date. And thanks for making the question more readable. — Trevor, Jun 13 '13 at 15:50
You're welcome. You should be aware that for such cases the customer and details will be left out of the results. I don't remember if ADS 9.10 supports `ROWID` (or `ROWNUM()`), but it could be used to simulate a primary key field and solve this issue. — Jens Mühlenhoff, Jun 13 '13 at 15:57
Oh thanks for the heads up, I just tested on ADS 9.10, ROWID and ROWNUM are both registered as key words. I'll give that a go! — Trevor, Jun 13 '13 at 16:08

score 1 · Answer 2 · answered Jun 12 '13 at 23:00

Your second LEFT JOIN is meant to exclude orders before the customers' most recent? I'd rewrite that as an EXISTS. I'm not familiar with Advantage DataBase, hopefully its implementation of SQL is not unusual.

SELECT
    Cust.Customer,
    Cust.Company,
    Inv.Date,
    Inv.Product,
    Inv.Units,
    Inv.Extended 
FROM
    customerlist AS Cust
    LEFT JOIN detail AS Inv ON Cust.customer = Inv.customer
WHERE
        (Inv.Product = 'CC' OR Inv.Product = 'CG' OR Inv.Product = 'CH')
    AND NOT EXISTS (SELECT * FROM detail AS Inv2 WHERE Cust.customer = Inv2.customer AND Inv2.date > inv.date)

You can simplify by replacing X = 'A' OR X = 'B' with X IN ('A', 'B'), assuming Advantage supports IN.

Note that, as written, this shows customers where their latest order is of type "CC", "CG", or "CH". If the goal is to show customers' latest order of that type, even if they have a subsequent order of another type, that would require an adjustment.

For example, if Bob has bought an AB, a BC, a CC, and a DE, he's excluded from the query above. If you want to include him because he's bought a CC product, though he later bought the DE, comment and I'll show how.

That's not solving the problem of missing customers, Trevor wants to have rows for every customer in the result even for customers where no corresponding details are found if I understood the problem correctly. — Jens Mühlenhoff, Jun 13 '13 at 09:38
@JensMühlenhoff: Hmm, you may be right. Though if so, I have no idea what the OP means to achieve with the second `LEFT JOIN`. It looks like you're on the right track, though, hopefully you can get the OP to his goal. — , Jun 13 '13 at 15:33
Have you read the linked question? http://stackoverflow.com/questions/2111384/sql-join-selecting-the-last-records-in-a-one-to-many-relationship — Jens Mühlenhoff, Jun 13 '13 at 15:34
It seems that OP took the second `LEFT JOIN` and `WHERE IS NULL` idea from the accepted answer, but confused the fields in the `ON` condition. — Jens Mühlenhoff, Jun 13 '13 at 15:35

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

2 Answers2

Linked