By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table Sales (
gather_id int,
id int
);
insert into Sales
select 1459 ,8011 from dual union all
select 1459 ,8022 from dual union all
select 1511 ,7011 from dual union all
select 1511 ,7022 from dual union all
select 1511 ,7033 from dual union all
select 1511 ,7044 from dual union all
select 1911 ,1011 from dual union all
select 1911 ,1022 from dual union all
select 1688 ,2011 from dual union all
select 1688 ,2022 from dual;
10 rows affected
create table Sales_Status (
id int,
info_status varchar(20)
);
insert into Sales_Status
select 8011, 'await_collect' from dual union all
select 8022, 'collected' from dual union all
select 7011, 'picking' from dual union all
select 7022, 'await_pick' from dual union all
select 7033, 'await_collect' from dual union all
select 7044, 'collected' from dual union all
select 1011, 'await_collect' from dual union all
select 1022, 'collected' from dual union all
select 2011, 'await_pick' from dual union all
select 2022, 'await_collect' from dual;
10 rows affected
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
| GATHER_ID | INFO_STATUS |
|---|---|
| 1459 | await_collect,collected |
| 1911 | await_collect,collected |