SQL to join tables and get value from one table that has most recent date closest to date of parent record

Question:

I have two tables, one a parent of the other, let's call them CUST and CUST_ADDR. Each table is a history table, so each time a value in one of the data fields changes, another record is added with the new value and a date it was changed. So let's just say for one Customer in the CUST table we have this:

CUST_ID     CHANGE_DATE   CUST_TITLE
1             JAN 20       CEO
1             JAN 15       MANAGER
1             JAN 10       ASSOCIATE
1             JAN 5        NEWBIE

The child table (CUST_ADDR) has the customer's address, again a new record is added every time it changes, so it might look like this:

CUST_ID    CHANGE_DATE        ADDRESS
1            JAN 18        1 MAIN STREET
1            JAN 16        2 ELM STREET
1            JAN 3         3 PINE DRIVE

I want to join these with the parent CUST table being the driver, so result set will have one row for each row in CUST, and for each row in CUST I want to return the address from CUST_ADDR that has the latest CHANGE_DATE before the change date for that row in the CUST table.

So my query results should be:

CUST_ID     CUST_CHANGE    CUST_TITLE  ADDR_CHANGE     ADDRESS

1             JAN 20        CEO         JAN 18       1 MAIN STREET
1             JAN 15        MANAGER     JAN 3        3 PINE DRIVE
1             JAN 10        ASSOCIATE   JAN 3        3 PINE DRIVE
1             JAN 5         NEWBIE      JAN 3        3 PINE DRIVE

Any solutions?

Answer:

You can try out any of these solutions

1. Window-function approach (recommended)

If you're on MySQL 8+, PostgreSQL, Oracle, or SQL Server, you can use the following code to join the parent history table to its child history table

SELECT  
  t.cust_id,  
  t.change_date AS cust_change,  
  t.cust_title,  
  t.addr_change,  
  t.address  
FROM (  
  SELECT  
 c.cust_id, 
 c.change_date, 
 c.cust_title, 
 ca.address,  
 ca.change_date AS addr_change, 
 RANK() OVER (  
  PARTITION BY c.cust_id, c.change_date  
  ORDER BY ca.change_date DESC  
 ) AS rnk  
  FROM CUST c  
  LEFT JOIN CUST_ADDR ca  
 ON ca.cust_id = c.cust_id  
  AND ca.change_date < c.change_date  
) t  
WHERE rnk = 1  
ORDER BY t.cust_id, t.change_date DESC;

What the code does:

• Joins all historical addresses up to each customer version.

• Ranks them by their date (most recent first).

• Filters to the top-ranked (latest) per customer-version pair.

2. LATERAL / APPLY Join (DB-specific)

If your version of MySQL supports it (or use PostgreSQL, SQL Server), you can try the following code

SELECT  
  c.cust_id,  
  c.change_date AS cust_change,  
  c.cust_title,  
  ca.change_date AS addr_change,  
  ca.address  
FROM cust AS c  
LEFT JOIN LATERAL (  
  SELECT change_date, address  
  FROM cust_addr ca  
  WHERE ca.cust_id = c.cust_id  
 AND ca.change_date <= c.change_date  
  ORDER BY ca.change_date DESC  
  LIMIT 1  
) AS ca ON TRUE  
ORDER BY c.cust_id, c.change_date DESC;

What the code does:

• For each customer version, the lateral subquery selects the single latest address before that point.

3. Correlated Subquery + JOIN (this works everywhere but may be less performant)

SELECT  
  c.cust_id,  
  c.change_date AS cust_change,  
  c.cust_title,  
  ca.change_date AS addr_change,  
  ca.address  
FROM cust c  
LEFT JOIN cust_addr ca  
  ON ca.cust_id = c.cust_id  
AND ca.change_date = ( 
   SELECT MAX(ca2.change_date) 
   FROM cust_addr ca2 
   WHERE ca2.cust_id = c.cust_id 
 AND ca2.change_date <= c.change_date 
 ) 
ORDER BY c.cust_id, c.change_date DESC;

To confirm what MAX() returns per row, test this:

SELECT  
  c.cust_id, 
  c.change_date AS cust_change, 
  ( 
 SELECT MAX(ca2.change_date) 
 FROM cust_addr ca2 
 WHERE ca2.cust_id = c.cust_id 
  AND ca2.change_date <= c.change_date 
  ) AS matched_change_date 
FROM cust c 
ORDER BY c.cust_id, c.change_date DESC;

Another way to solve this is using ROW_NUMBER() inside a CTE. This technique is great for complex debugging.

 WITH ranked_addresses AS ( 
  SELECT 
 ca.cust_id, 
 ca.change_date, 
 ca.address, 
 c.change_date AS cust_change, 
 ROW_NUMBER() OVER ( 
  PARTITION BY c.cust_id, c.change_date 
  ORDER BY ca.change_date DESC 
 ) AS rn 
  FROM cust c 
  LEFT JOIN cust_addr ca 
 ON ca.cust_id = c.cust_id 
   AND ca.change_date <= c.change_date 
) 
SELECT  
  cust_id, 
  cust_change, 
  change_date AS addr_change, 
  address 
FROM ranked_addresses 
WHERE rn = 1 
ORDER BY cust_id, cust_change DESC;

Alternatively, you can use dbForge Studio for MySQL, PostgreSQL, or SQL Server (depending on your database system), or dbForge Edge to explore data history, generate scripts, and test queries. It handles parent-child object relationships intuitively and is excellent for prototyping LATERAL joins and window functions.