Michael meshaev Michael meshaev - 2 years ago 60
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

statement because it works like
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
statement because the proc is dynamic and generates complex code.

WHERE R.RiskID IN(111,222)

Again, it works like
statement. The defenition table have a row for each
, means if I have many
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

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download