Extracting specific value from table in oracle

Question

I have below two tables(Sales and Sales_Status) with below sets of data:

Table Sales holds gather_id and id column. Each id have info status present sales_Status table. I need to frame SQL query in oracle to fetch only those gather_id where info_status contain status as await_collect and collected. Could you please help me to frame SQL query for such scenario. Thanks.

Sales

gather_id     | id
|1459        |8011
|1459        |8022
|1511        |7011
|1511        |7022
|1511        |7033
|1511        |7044
|1911        |1011
|1911        |1022
|1688        |2011
|1688        |2022

Sales_Status

id      info_status 
8011    await_collect   
8022    collected   
7011    picking 
7022    await_pick  
7033    await_collect   
7044    collected   
1011    await_collect   
1022    collected   
2011    await_pick  
2022    await_collect   

Output should look like:

Gather id       info_status
1459            await_collect,collected
1911            await_collect,collected

Answer 1

This can be done using group by and the having clause :

The condition count(distinct ss.info_status) = 2 selects only the gather_id with only 2 info_status.

The two others count( case when ..) = 1 checks that the info_status is await_collect and collected

select sa.gather_id, listagg(ss.info_status, ',') as info_status
from Sales sa
inner join Sales_Status ss on sa.id = ss.id
group by sa.gather_id
having count(distinct ss.info_status) = 2
and count(case when ss.info_status='await_collect' then 1 end) = 1
and count(case when ss.info_status='collected' then 1 end) = 1

Result :

GATHER_ID	INFO_STATUS
1459	await_collect,collected
1911	await_collect,collected

Demo here