CODEWITHSUNDEEP
sqlsql-server-2014
webworm
webworm

Reputation: 11019

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.

Upvotes: 2

Views: 52

Answers (3)

Rich Benner
Rich Benner

Reputation: 8113

You'd probably find that this performs better using a WHERE EXISTS like this

SELECT *
FROM Table1 t1
WHERE EXISTS (  SELECT * 
                FROM Exam e 
                WHERE t1.Exam1_ID = e.Id
                OR t1.Exam2_ID = e.Id
                OR t1.Exam3_ID = e.Id
                OR t1.Exam4_ID = e.Id)

Upvotes: 3

Baximilian
Baximilian

Reputation: 885

Lets begin from error message

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

The reason is pretty simple, you declared your subquery as INT So if you will change it to TABLE(Id INT) you will receive new error message.

You can use different ways to solve your problem.

1) Inner join (It can give you duplicates, so I dont know is it valid for you to use this method)

DECLARE @SubQuery TABLE (RecordId INT)

INSERT INTO 
    @SubQuery
SELECT 
    Id
FROM 
    Exam

SELECT * 
FROM 
   Table1 t1
INNER JOIN @Subquery sq 
  ON sq.Id = t1.Exam1_ID
    OR sq.Id = t1.Exam2_ID
    OR sq.Id = t1.Exam3_ID
    OR sq.Id = t1.Exam4_ID

2) Exist

Please find sample in Rich Benner answer. He posted it while I was typing. But I found one typo there, he used different aliases in exists block

 OR t1.Exam2_ID = e.Id --t1 - correct alias
 OR t2.Exam2_ID = e.Id --t2 - incorrect
 OR t3.Exam2_ID = e.Id --t3 - incorrect
 OR t4.Exam2_ID = e.Id --t4 - incorrect

Upvotes: 2

Abdul Rehman Sayed
Abdul Rehman Sayed

Reputation: 6662

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

Upvotes: 2

Related Questions