Michael meshaev - 1 year ago 35

SQL Question

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

`IN`

`OR`

`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`

example:

`WHERE R.RiskID IN(111,222)`

Again, it works like

`OR`

`RiskID`

`RiskIDs`

`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.

Source (Stackoverflow)