SQL Multiple Row Subquery

Question

I have a table Studies that I perform a SELECT on.

I then need to perform a further SELECT on the recordset returned. I've tried this (simplified for clarity):

SELECT * FROM Studies
WHERE Id = '2' OR Id = '3' OR Id = '7';

SELECT * FROM Studies
WHERE (Name = 'Test')
  AND Id IN (SELECT * FROM Studies WHERE Id = '2' OR Id = '3' OR Id = '7');

But I keep getting the following SQL error:

Only a single result allowed for a SELECT that is part of an expression

---

Where am I going wrong? If it's not evident from my code - I am relatively new to database programming.

Thanks

Answer 1

You can't return more than one column in a IN (...) subquery. You have to change the * (return all columns) to ID. But your query does not need a subquery, You can just add the ID's to the first query. You usually want to avoid subqueries where you can because of performance reasons.

SELECT * 
FROM Studies 
WHERE Name = 'Test' 
AND ID IN ('2', '3','7')

Or if you want to keep your structure:

SELECT * 
FROM Studies 
WHERE (Name = 'Test') 
AND ID IN (SELECT ID FROM Studies WHERE ID = '2' OR ID = '3' OR ID = '7');

Answer 2

SELECT * FROM Studies WHERE (Name = 'Test') AND ID IN (SELECT ID FROM Studies WHERE ID = '2' OR ID = '3' OR ID = '7');