Combinations in SQL

Question

A

ID      ID_DESC1
1    123              
1    124
1    126
2    127
2    124
2    126
3    130
3    124
3    126
4    131 
4    124
4    126

B

ID_DESC1    DESC
123       ACTIVE
124       SAR
126       SAR1
127       REAL
130       TRI
131       ACC

Query should fetch the only ID's for Table A join B with the following combinations Result Expected

Combination1: - ACTIVE,SAR,SAR1
Combination2: - REAL,SAR,SAR1
Combination3: - ACC,SAR,SAR1

Result shoiuld only fetch ID 1,2,4 and not 3

Answer 1

This is an example of a group-with-g

select a.id
from a join
     b
     on a.id_desc1 = b.id_desc1
group by a.id
having sum(case when b.desc = 'SAR' then 1 else 0 end) > 0 and
       sum(case when b.desc = 'SAR1' then 1 else 0 end) > 0 and
       sum(case when b.desc in ('ACTIVE', 'REAL', 'ACC') then 1 else 0 end) > 0;       

Answer 2

The above query can be answered by LISTAGG with in oracle