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

Please don’t post images, always use editable text. Can you also update your question with the result you want to achieve, based on your sample data, and the SQL you’ve managed to write on your own — NickW, Jun 03 '23 at 20:19
Please [edit] the question to include a [MRE] with: the `CREATE TABLE` and `INSERT` statements for your sample data as text we can copy/paste (and not as images, which are not accessible to all users); **YOUR** attempt at a solution; the issues/errors with **YOUR** solution; and the expected output for that sample data. StackOverflow is not a free code-writing service; **YOU** need to attempt the problem first and then can ask for help debugging. — MT0, Jun 03 '23 at 20:19

score 1 · Accepted Answer · answered Jun 03 '23 at 22:52

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

Extracting specific value from table in oracle

1 Answers1