How to use columns from the left table in where clause in the right select statement in SQL Server join statement

Question

I want to join a table with the output of select statement where I need to use a column last_order_date from Table1 in the WHERE clause in the select statement.

For example, see this screenshot:

I tried the following code:

SELECT 
    * 
FROM
    Table1 t1
LEFT JOIN 
    (SELECT prod_id, SUM(sales) sales_sum
     FROM Table2
     WHERE transaction_date BETWEEN t1.last_order_date AND CAST(GETDATE()-1 AS DATE)
     GROUP BY prod_id) t2 
ON t1.prod_id = t2.prod_id

Apparently, the problem is that I can't use t1.last_order_date in the WHERE CLAUSE in the right part of the join statement. Anyone can help?

Answer 1

You can try to use OUTER APPLY

SELECT * 
FROM
    Table1 t1
OUTER APPLY 
(
     SELECT t2.prod_id, SUM(t2.sales) sales_sum
     FROM Table2 t2
     WHERE t2.transaction_date BETWEEN t1.last_order_date AND CAST(GETDATE()-1 AS DATE)
     AND  t1.prod_id = t2.prod_id
     GROUP BY t2.prod_id
) t2

From your expect result I think you can use LEFT JOIN like this.

SELECT t1.prod_id,
       MIN(t1.last_order_date) last_order_date,
       SUM(ISNULL(t2.sales,0)) sales
FROM
    Table1 t1
LEFT JOIN Table2 t2
ON t2.transaction_date BETWEEN t1.last_order_date 
     AND CAST(GETDATE()-1 AS DATE)
     AND  t1.prod_id = t2.prod_id
GROUP BY t1.prod_id