CODEWITHSUNDEEP
sqloracle-database
confusedbeginner
confusedbeginner

Reputation: 131

Inner Joins and retrieving the parent record if only its children record met certain criteria

Table CP 

CP_Name  CP_Number  CP_PK
-------  ---------  -----
AA             111    123
BB             222    124
CC             333    125
DD             444    126
EE             555    127
FF             666    128
GG             777    129

Table PP

PP_Name  PP_Number  PP_Phase    PP_PK  Parent_PK
-------  ---------  ----------  -----  ---------
ABC          11111  Active        345        123
DEF          22222  Inactive      346        123
GHI          33333  Inactive      347        124
JKL          44444  Terminated    348        124
MNO          55555  TDR           349        126
PQR          66666  Active        350        126
STU          77777  Inactive      351        127
VWX          88888  Active        352        128
YX           99999  TDR           353        129
BCA          12121  Terminated    354        126

Results:

CP_Name  CP_Number  PP_Name  PP_Number  PP_Phase    CP_PK  Parent_PK
-------  ---------  -------  ---------  ----------  -----  ---------
AA             111  ABC          11111  Active        123        123
AA             111  DEF          22222  Inactive      123        123
DD             444  MNO          55555  TDR           126        126
DD             444  PQR          66666  Active        126        126
DD             444  BCA          12121  Terminated    126        126

I have two tables related by CP_PK = Parent_PK. Table PP is a child of Table CP.

I’m trying to find cases where Table CP has at least two children rows (could be more than two), where one of the child could be equal to Inactive, Terminated or Transferred to TPD and then the other child could be equal to Active, TDR Approved, TDR Draft, TDR Final. I started with an inner join of the two tables based off CP_PK = Parent_PK and then using PP_Phase = 'xxx' and a Group By, but quickly realized that wouldn’t work. Any suggestions/ideas?

Upvotes: 2

Views: 99

Answers (3)

user5683823
user5683823

Reputation:

This is what Boneist mentioned in his/her first Comment under your original post. Adapt as needed; this is not exactly what you need, but it should be sufficiently close and you should be able to modify it yourself.

with
     cp ( cp_name, cp_number, cp_pk ) as (
       select 'AA', 111, 123 from dual union all
       select 'BB', 222, 124 from dual union all
       select 'CC', 333, 125 from dual union all
       select 'DD', 444, 126 from dual union all
       select 'EE', 555, 127 from dual union all
       select 'FF', 666, 128 from dual union all
       select 'GG', 777, 129 from dual
     ),
     pp ( pp_name, pp_number, pp_phase, pp_pk, parent_pk ) as (
       select 'ABC', 11111, 'Active'    , 345, 123 from dual union all
       select 'DEF', 22222, 'Inactive'  , 346, 123 from dual union all
       select 'GHI', 33333, 'Inactive'  , 347, 124 from dual union all
       select 'JKL', 44444, 'Terminated', 348, 124 from dual union all
       select 'MNO', 55555, 'TDR'       , 349, 126 from dual union all
       select 'PQR', 66666, 'Active'    , 350, 126 from dual union all
       select 'STU', 77777, 'Inactive'  , 351, 127 from dual union all
       select 'VWX', 88888, 'Active'    , 352, 128 from dual union all
       select 'YX' , 99999, 'TDR'       , 353, 129 from dual union all
       select 'BCA', 12121, 'Terminated', 354, 126 from dual
     )
select cp_pk, cp_name, cp_number, pp_pk, pp_name, pp_number, pp_phase
from   ( select cp_pk, cp_name, cp_number, pp_pk, pp_name, pp_number, pp_phase,
                count(case when pp_phase in ('Active', 'TDR')          then 1 end) 
                                                     over (partition by cp_pk) as flag_1,
                count(case when pp_phase in ('Inactive', 'Terminated') then 1 end) 
                                                     over (partition by cp_pk) as flag_2
         from   cp join pp on cp.cp_pk = pp.parent_pk
       )
where  flag_1 > 0 and flag_2 > 0
;

Output:

CP_PK CP_NAME  CP_NUMBER PP_PK PP_NAME  PP_NUMBER PP_PHASE
----- ------- ---------- ----- ------- ---------- ----------
  123 AA             111   346 DEF          22222 Inactive
  123 AA             111   345 ABC          11111 Active
  126 DD             444   350 PQR          66666 Active
  126 DD             444   354 BCA          12121 Terminated
  126 DD             444   349 MNO          55555 TDR

5 rows selected.

Upvotes: 1

MT0
MT0

Reputation: 167972

SELECT cp.*
FROM
  CP
  INNER JOIN
  (
    SELECT parent_pk
    FROM   PP
    GROUP BY parent_pk
    HAVING COUNT(
             CASE WHEN Parent_PK IN ( 'Inactive', 'Terminated', 'Transferred to TPD'
                  THEN 1 END
           ) >= 1
    AND    COUNT(
             CASE WHEN Parent_PK IN ( 'Active', 'TDR Approved', 'TDR Draft', 'TDR Final'
                  THEN 1 END
           ) >= 1
  ) pp
  ON ( cp.cp_pk = pp.parent_pk )

Upvotes: 1

Vecchiasignora
Vecchiasignora

Reputation: 1315

Try this

select * from (select Count(*) OVER (partition by CP.CP_PK) cnt, CP.* from CP              
                INNER JOIN PP  ON CP.CP_PK = PP.Parent_PK where some Condition1  or some condition2) a where a.cnt>1

Upvotes: 1

Related Questions