Michael meshaev Michael meshaev - 7 months ago 10
SQL Question

sql where statement with multiple values

In my WHERE statement I'm trying to find some entities the have BOTH conditions, I cannot use the

IN
statement because it works like
OR
and returning only one result of many, means if I am using:

WHERE R.RiskID IN(221,111)


So I will get the Documents that have or 111 OR 222
its a problem for me to use the
AND
statement because the proc is dynamic and generates complex code.
example:

WHERE R.RiskID IN(111,222)


Again, it works like
OR
statement. The defenition table have a row for each
RiskID
, means if I have many
RiskIDs
for same document it looks like:

RiskID DocumentID
111 345
222 345
333 345
999 846
111 846


my final destinations should be, if my input is 111,222 I need to show all the Documents that have 111 AND 222
RiskIDs
.

Answer

if anybody have the same problem so the answer is COMBINATIONS and PERMUTATIONS... ;with list (n) as ( select * from ( values ('a'), ('b'), ('c'), ('d'), ('e') ) x(y) ), cte as ( select CAST( n AS NVARCHAR(MAX) ) n from list c union all select CAST( l.n + ',' + c.n AS NVARCHAR(MAX) ) from cte c, list l where l.n > c.n ) select * from cte

the generator generates all the possible combinations for the VALUES that in the FROM clouse.

Comments