Check if a result set has specific values

Question

I want to know what is the best way to check if a subquery has a set of values. I came up with the following query but I don't think its efficient.

SELECT * FROM SomeTable 
WHERE 
(
    2 IN (SELECT OptionId FROM Table2 WHERE Table2_Id = 1) AND
    3 IN (SELECT OptionId FROM Table2 WHERE Table2_Id = 1)
)

I appreciate if anyone can provide me with a better solution.

Thanks

your query will eather return no rows or all rows. Maybe you should describe what you need — t-clausen.dk, Aug 05 '13 at 07:23
Assume I have an entity that can have some options, I want to get entities that have at least specified options (in this sample 2 and 3). — mrtaikandi, Aug 05 '13 at 07:32
No matter which numbers you put in to replace 2 and 3. You will eather get all rows or no rows from Sometable. I believe you need a different result. Describe what you need and the relation between SomeTable and Table2 — t-clausen.dk, Aug 05 '13 at 08:00
I have an Entities Table which has a many to many relation with Options table which results in a middle table that in the sample I called it Table2. I want to get all entities that have options with id 2 AND 3. — mrtaikandi, Aug 05 '13 at 10:29
If the query I came up with is incorrect, I appreciate if you can provide a correct query for the above question. — mrtaikandi, Aug 05 '13 at 10:30
@Mohammadreza: You should post the real problem not something which is a half solution without depicting the full problem. — Bogdan Sahlean, Aug 05 '13 at 15:11

Bogdan Sahlean · Accepted Answer · 2013-08-05T15:55:04.280

I would use EXISTS subqueries:

SELECT * FROM SomeTable 
WHERE EXISTS (SELECT * FROM Table2 WHERE Table2_Id = 1 AND OptionId = 2) 
AND   EXISTS (SELECT * FROM Table2 WHERE Table2_Id = 1 AND OptionId = 3)

If you have one of the following indexes (Table2_Id,OptionId) or (OptionId,Table2_Id) then you shouldn't have worries about performance.

Edit 1: After reading above comments I think you should JOIN somehow SomeTable with Table2 using a common column thus:

SELECT * FROM SomeTable x
WHERE EXISTS (SELECT * FROM Table2 y WHERE y.Table2_Id = 1 AND y.OptionId = 2 AND x.CommonColumn = y.CommonColumn) 
AND   EXISTS (SELECT * FROM Table2 y WHERE y.Table2_Id = 1 AND y.OptionId = 3 AND x.CommonColumn = y.CommonColumn)

If this doesn't solve your problem then you should add more infos.

Check if a result set has specific values

1 Answers1