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

Questions

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

April 5, 2022

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?

>Solution :

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 OUTER 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