SQL multi-row subquery

Question

I have a Studies table in which I perform a SELECT.

Then I need to do another SELECT on the returned recordset. I 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 one result allowed for SELECT, which is part of the expression

---

Where am I mistaken? If this is not obvious from my code, I am relatively new to database programming.

thanks

Answer 1

You cannot return more than one column in the IN (...) subquery. You must change * (return all columns) to ID . But your request does not need a subquery, you can simply add an identifier to the first request. Usually you want to avoid subqueries where you can for 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');