use values from one data table as input for another table where command in sql

I have this sample query in which I want data extracted in a column (var5) in table1 as input in where command in table2 and then join the tables. Can you please tell how to do this. The issue at hand is this last where command at the end of table2. dbo.var4 IN ( t1.var5 )

SELECT DISTINCT
    t1.var1, t1.var2, t1.var3, t1.var4, t1.var5, 
    t2. var1, t2.var2, t2.var3
FROM
    /*table 1 = t1*/
    (SELECT
         dbo.var1, dbo.var2, dbo.var3,
         dbo.var4, dbo.var5,
     FROM
         dbo.data_table1 
     INNER JOIN 
         dbo.data_table2 ON (dbo.data_table2.var1 = dbo.data_table1.var1)
     WHERE
         (dbo.var1 IN ('name1')
          AND dbo.var2 IN ('Y')
          AND dbo.var3 IN ('Y')
          AND dbo.var4 IN ('Y'))) AS t1
    LEFT OUTER JOIN  
        /*--------------------------- 2. table 2 ---------------------------*/
        (SELECT
             dbo.var1, dbo.var2, dbo.var3,
         FROM
             dbo.data_table3 
         INNER JOIN 
             dbo.data_table4 ON (dbo.data_table4.var1 = dbo.data_table3.var4)
         WHERE
             (dbo.var1 IN (-1)
              AND dbo.var2 IN ('Y')
              AND dbo.var4 IN (t1.var5))) AS t2 ON t2.var4 = t1.var5   

Thank you

Answer

Before I answer your question, I would like to make some remarks regarding your question.

1. In the future, please tag your question with the specific DBMS you are using. Since your query is using a dbo schema with your tables, I assume you are using Microsoft SQL Server. So my answer will be focused on Microsoft SQL Server.

2. You also use the prefix dbo. directly with column names in your query. Table names seem to be missing, which will result in errors. In my answer, I removed all invalid dbo usages; you will need to add the full prefixes again yourself (like you did in the JOINs).

3. The SELECT-clauses of the table expressions t1 and t2 are incomplete and invalid, since they end with commas. This will result in errors as well. I removed these commas in my answer.

Furthermore, I would like to learn why you use the IN-operator instead of the =-operator when comparing only single values. It’s perfectly valid, of course, but it just seems to be unnecessary. I kept them in my answer, since you seem to prefer them.

Solution 1:

You need to include var4 in the SELECT-clause of table expression t2, so that it becomes visible from the “outside” (the main query) and thus can be used in the ON-part of the join between t1 and t2.

SELECT DISTINCT
    t1.var1, t1.var2, t1.var3, t1.var4, t1.var5, 
    t2. var1, t2.var2, t2.var3
FROM
    /*table 1 = t1*/
    (SELECT
         var1, var2, var3,
         var4, var5
     FROM
         dbo.data_table1 
     INNER JOIN 
         dbo.data_table2 ON dbo.data_table2.var1 = dbo.data_table1.var1
     WHERE
         var1 IN ('name1')
         AND var2 IN ('Y')
         AND var3 IN ('Y')
         AND var4 IN ('Y')) AS t1
    LEFT OUTER JOIN
        /*--------------------------- 2. table 2 ---------------------------*/
        (SELECT
             var1, var2, var3, var4
         FROM
             dbo.data_table3 
         INNER JOIN 
             dbo.data_table4 ON dbo.data_table4.var1 = dbo.data_table3.var4
         WHERE
             var1 IN (-1)
             AND var2 IN ('Y')) AS t2 ON t2.var4 = t1.var5

Solution 2:

Alternatively, you could use OUTER APPLY instead of LEFT JOIN:

SELECT DISTINCT
    t1.var1, t1.var2, t1.var3, t1.var4, t1.var5, 
    t2. var1, t2.var2, t2.var3
FROM
    /*table 1 = t1*/
    (SELECT
         var1, var2, var3,
         var4, var5
     FROM
         dbo.data_table1 
     INNER JOIN 
         dbo.data_table2 ON dbo.data_table2.var1 = dbo.data_table1.var1
     WHERE
         var1 IN ('name1')
         AND var2 IN ('Y')
         AND var3 IN ('Y')
         AND var4 IN ('Y')) AS t1
    OUTER APPLY
        /*--------------------------- 2. table 2 ---------------------------*/
        (SELECT
             var1, var2, var3, var4
         FROM
             dbo.data_table3 
         INNER JOIN 
             dbo.data_table4 ON dbo.data_table4.var1 = dbo.data_table3.var4
         WHERE
             var1 IN (-1)
             AND var2 IN ('Y')
             AND var4 IN (t1.var5)) AS t2