Each record in Table E has:

1. A Unique ID [Entry_Number]
2. A Group Key [Group] (used to associate records together, not unique, corresponds to foreign table G where it is unique key)
3. A Status (Character indicators of a real-life process) (A, R, C, I) [Entry_Status]

Each record in Table G has:

1. A unique ID (corresponding to Group Key) [Group_Number]
2. A Group Status (True or false)[Group_Status]

I want to query for Entry Numbers from Table E where Entry_Status = A or R, and when all other entries with the same GROUP_NUMBER also have ENTRY_STATUS = A OR R (the part I am struggling to figure out) and when the Group_status for that Group_Number = FALSE (ignore ALL entries w/ a group_number associated with TRUE group_status regardless of the Entry_Status).

Example:

Table E: [12, 1, A] [13, 1, A] [14, 1, R] [15, 2, A] [16, 2, I] [17, 3, A] [18, 3, C]

Table G: [1,False] [2,False] [3,True]

After running my idealized query, I should get [12,13,14] returned. Group 2 is rejected since the status of entry 17 = I and Group 3 is rejected because the 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 into account the other entries with the same group_number.)

How do I relate entries within the same table according to the Group_Number field and then checking the 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'