How to remove temp tables from logic in stored procedure query

Question

I need to improve the stored procedure sample code shown here to remove all temp tables, by using one query code or CTE maybe?

Note that it is necessary to create the first temp table to get specific users ID list, then create the second temp table with another specific criteria, then join with both temp tables created to get the final results table.

SELECT    
    Column_1,
    Column_2,
    Column_3    
INTO 
    #TempTable_1    
FROM
    TABLE_1    
WHERE 
    Column_1 = 1

SELECT    
    Column_1,
    Column_4,
    Column_5,
    Column_6    
INTO
    #TempTable_2    
FROM 
    TABLE_2    
WHERE 
    Column_4 = 4    

SELECT
    A.Column_1,
    A.Column_2,
    A.Column_3,
    B.Column_4,
    B.Column_5,
    B.Column_6    
FROM
    #TempTable_2 B    
INNER JOIN 
    #TempTable_1 A ON A.Column_1 = B.Column_1

Answer 1

In addition to Dale's sub-query, you can use a CTE

with cte1 as (
SELECT    
    Column_1,
    Column_2,
    Column_3    
FROM TABLE_1    
WHERE Column_1 = 1
),  cte2 as (
SELECT    
    Column_1,
    Column_4,
    Column_5,
    Column_6    
FROM TABLE_2    
WHERE Column_4 = 4    
)
SELECT
    A.Column_1,
    A.Column_2,
    A.Column_3,
    B.Column_4,
    B.Column_5,
    B.Column_6    
FROM cte2 B    
INNER JOIN cte1 A ON A.Column_1 = B.Column_1