SQL Server 2008: If Multiple Values Set In Other Mutliple Values Set
- by AJH
In SQL, is there anyway to accomplish something like this? This is based off a report built in SQL Server Report Builder, where the user can specify multiple text values as a single report parameter. The query for the report grabs all of the values the user selected and stores them in a single variable. I need a way for the query to return only records that have associations to EVERY value the user specified.
-- Assume there's a table of Elements with thousands of entries.
-- Now we declare a list of properties for those Elements to be associated with.
create table #masterTable (
ElementId int, Text varchar(10)
)
insert into #masterTable (ElementId, Text) values (1, 'Red');
insert into #masterTable (ElementId, Text) values (1, 'Coarse');
insert into #masterTable (ElementId, Text) values (1, 'Dense');
insert into #masterTable (ElementId, Text) values (2, 'Red');
insert into #masterTable (ElementId, Text) values (2, 'Smooth');
insert into #masterTable (ElementId, Text) values (2, 'Hollow');
-- Element 1 is Red, Coarse, and Dense. Element 2 is Red, Smooth, and Hollow.
-- The real table is actually much much larger than this; this is just an example.
-- This is me trying to replicate how SQL Server Report Builder treats
-- report parameters in its queries. The user selects one, some, all,
-- or no properties from a list. The written query treats the user's
-- selections as a single variable called @Properties.
-- Example scenario 1: User only wants to see Elements that are BOTH Red and Dense.
select e.*
from Elements e
where (@Properties) --ideally a set containing only Red and Dense
in
(select Text from #masterTable where ElementId = e.Id) --ideally a set containing only Red, Coarse, and Dense
--Both Red and Dense are within Element 1's properties (Red, Coarse, Dense), so Element 1 gets returned, but not Element 2.
-- Example scenario 2: User only wants to see Elements that are BOTH Red and Hollow.
select e.* from Elements e where
(@Properties) --ideally a set containing only Red and Hollow
in
(select Text from #masterTable where ElementId = e.Id)
--Both Red and Hollow are within Element 2's properties (Red, Smooth, Hollow), so Element 2 gets returned, but not Element 1.
--Example Scenario 3: User only picked the Red option.
select e.* from Elements e where
(@Properties) --ideally a set containing only Red
in
(select Text from #masterTable where ElementId = e.Id)
--Red is within both Element 1 and Element 2's properties, so both Element 1 and Element 2 get returned.
The above syntax doesn't actually work because SQL doesn't seem to allow multiple values on the left side of the "in" comparison. Error that returns:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Am I even on the right track here? Sorry if the example looks long-winded or confusing.
