webworm webworm - 26 days ago 7
SQL Question

Setting subquery to variable to be used within the IN operator

I would like to use a variable to represent a result set that will be used in the WHERE clause of a query.

SELECT *
FROM Table1
WHERE
Exam1_ID IN (SELECT Id FROM Exam)
OR Exam2_ID IN (SELECT Id FROM Exam)
OR Exam3_ID IN (SELECT Id FROM Exam)
OR Exam4_ID IN (SELECT Id FROM Exam)


I would like to use a variable in place of
SELECT Id FROM Exam
so I don't have to keep repeating the query. I tried declaring a variable but since the results of the subquery could contain multiple integers I am not sure what the declare the variable as. I went ahead and tried ...

DECLARE @SubQuery INT;
SET @SubQuery = (SELECT Id FROM Exam);

SELECT *
FROM Table1
WHERE
Exam1_ID IN (@SubQuery)
OR Exam2_ID IN (@SubQuery)
OR Exam3_ID IN (@SubQuery)
OR Exam4_ID IN (@SubQuery)


I received the following error ..

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Answer

You can write an exists like this.

      SELECT *
        FROM Table1 t1
        WHERE EXISTS (  
        SELECT 1  FROM Exam e 
        WHERE e.Id in ( t1.Exam1_ID , t1.Exam2_ID , t1.Exam3_ID, t1.Exam4_ID ) 
        )
Comments