How to query for this specific result?-CodePudding

Table E :

Unique ID Entry_Number.
Group key Group (to associate records together, not unique, corresponds to foreign table G where it is unique key).
Status Entry_Status (character indicators of a real-life process: A, R, C, I).

Table G :

Unique ID Group_Number (corresponding to group key).
Group status Group_Status (true or false).

I want to query for entry numbers from table E where Entry_Status = A or R, and if entries with same Group_Number also have Entry_Status = A or R (the part I can't figure out) and if Group_status for that Group_Number = false (ignore entries with Group_Number associated with TRUE Group_Status regardless of Entry_Status). Example:

Table E:

Entry_Number	Group	Entry_Status
12	1	A
13	1	A
14	1	R
15	2	A
16	2	I
17	3	A
18	3	C

Table G:

Group_Number	Group_Status
1	False
2	False
3	True

I should get [12,13,14]. Group 2 is rejected since status of entry 17 = I and Group 3 is rejected because Group_Status = True.

SELECT ENTRY_NUMBERS
FROM ENTRY E, GROUP G
WHERE G.GROUP_STATUS = 'FALSE'
AND E.STATUS IN ('A','R')

This does not take entries with same Group_Number into account. How do I relate entries within same table according to Group_Number, then checking status of those other entries to decide if the original should be considered?

CodePudding user response：

Something like this?

Sample data:

SQL> with
  2  entry (entry_number, group_number, status) as
  3    (select 12, 1, 'A' from dual union all
  4     select 13, 1, 'A' from dual union all
  5     select 14, 1, 'R' from dual union all
  6     select 15, 2, 'A' from dual union all
  7     select 16, 2, 'I' from dual union all
  8     select 17, 3, 'A' from dual union all
  9     select 18, 3, 'C' from dual
 10    ),
 11  t_group (group_number, status) as
 12    (select 1, 'False' from dual union all
 13     select 2, 'False' from dual union all
 14     select 3, 'True'  from dual
 15    )

Query begins here:

 16  select e.entry_number
 17  from entry e
 18  where e.status in ('A', 'R')
 19    and not exists (select null
 20                    from t_group g
 21                    where g.group_number = e.group_number
 22                      and g.status = 'True'
 23                   )
 24    and not exists (select null
 25                    from entry e1
 26                    where e1.group_number = e.group_number
 27                      and e1.status not in ('A', 'R')
 28                   )
 29  order by e.entry_number;

ENTRY_NUMBER
------------
          12
          13
          14

SQL>

CodePudding user response：

You can use window functions and a conditional expression to check the status of other entries of the same group, and join the other table to filter on the group status:

select e.entry, e.numbers
from (
    select e.*, 
        min(case when group_status in ('A', 'R') then 1 else 0 end) 
            over(partition by group_number) as keep_row
    from t_entry e
) e
inner join t_group g on g.group_number = e.group_number
where e.keep_row = 1 and g.group_status = 'FALSE'