Skip to Main Content
  • Questions

  • SQL query to find FK IDs with a series of values

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Bhogesh.

Asked: March 20, 2019 - 5:07 pm UTC

Last updated: April 08, 2019 - 11:11 am UTC

Version: 12.1

Viewed 1000+ times

You Asked

we have below tables 

create table bca(id number(2));

insert into  bca(10);
insert into  bca(11);
insert into  bca(7);

create TABLE abc(
    di      NUMBER(2),
    fk_id   NUMBER(3),
    yek     VARCHAR2(20),
    elv   VARCHAR(15)
)

Insert into ABC (DI,FK_ID,YEK,ELV) values (1,10,'DATE','20190320');
Insert into ABC (DI,FK_ID,YEK,ELV) values (2,10,'EXECUTED','Y');
Insert into ABC (DI,FK_ID,YEK,ELV) values (3,11,'DATE','20190320');
Insert into ABC (DI,FK_ID,YEK,ELV) values (4,11,'EXECUTED','N');
Insert into ABC (DI,FK_ID,YEK,ELV) values (5,7,'DATE','20190321');
Insert into ABC (DI,FK_ID,YEK,ELV) values (6,7,'EXECUTED','Y');


I have tried with below query

( SELECT
id ,di,fk_id,yek,elv
FROM
    bca 
    INNER JOIN abc
    ON(id=fk_id)
WHERE
  --  fk_cmcm_component = 'IRC' AND cmmu_task_type = 'F'
    (YEK,ELV)IN(('DATE','20190320'),('EXECUTED','Y')))


below results for above query 

10 1 10 DATE    20190320
10 2 10 EXECUTED Y
11 3 11 DATE    20190320
7 6 7 EXECUTED Y


But I required only fk_id 10 results because I am passing 20190320 for YEK and 'Y' for ELV.

required result is 

10 1 10 DATE    20190320
10 2 10 EXECUTED Y


Please help me.

and Chris said...

That's because your query returns any row where either

yek = 'DATE' and elv = '20190320'


OR

yek = 'EXECUTED' and elv = 'Y'


Is true.

You need to find FK_IDs that have both of these rows. This is a problem for pattern matching!

The pattern you want is a DATE row, followed by EXECUTED.

So you need to define pattern variables for each of these. This checks the YEK & ELV columns have the values you're looking for.

It's per FK_ID, so you need to partition by this. And sort by a column which defines the order they were inserted (DI here).

Which gives:

select * from abc
match_recognize ( 
  partition by fk_id
  order by di
  all rows per match
  pattern ( dt exe )
  define 
    dt as yek = 'DATE'
       and elv = '20190320',
    exe as yek = 'EXECUTED'
        and elv = 'Y'
);

FK_ID   DI   YEK        ELV        
     10    1 DATE       20190320   
     10    2 EXECUTED   Y

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Older Versions

AndyP, March 22, 2019 - 10:13 am UTC

Just speculating as to how to solve this requirement pre-12c and came up with this approach, which seems to work

I'm assuming the data presentation to be quite strict - 2 ordered rows per fk, always DATE and EXECUTED, but I think the match_recognize solution does that too

with abc as
(
select 1 di,10 fk_id,'DATE' yek,'20190320' elv from dual union all
select 2,10,'EXECUTED','Y' from dual union all
select 3,11,'DATE','20190320' from dual union all
select 4,11,'EXECUTED','N' from dual union all
select 5,7,'DATE','20190321' from dual union all
select 6,7,'EXECUTED','Y' from dual
)
,data as
(
select fk_id,yek,elv
      ,row_number() over (partition by fk_id,yek order by di) as rn
  from abc
)
,pivoted as
(
select * from data
pivot (min(elv) elv for yek in ('DATE' c1,'EXECUTED' c2))
)
select abc.*
  from abc,pivoted
 where abc.fk_id = pivoted.fk_id
   and pivoted.c1_elv = '20190320'
   and pivoted.c2_elv = 'Y'
/

        DI      FK_ID YEK      ELV
---------- ---------- -------- --------
         1         10 DATE     20190320
         2         10 EXECUTED Y


Chris Saxon
March 22, 2019 - 10:59 am UTC

Neat. Yep, the MR solution assumes EXECUTE immediately after DATE. There are ways around this in MR though.

I like the PM approach, old fashioned as I am, I would have used a windowing function in this case.

cd, March 25, 2019 - 12:25 pm UTC 

SELECT di,fk_id,yek,elv
  FROM (SELECT abc.*
             , COUNT(*) OVER (PARTITION BY fk_id) num_cond
          FROM abc
         WHERE (yek, elv) IN (( 'DATE', '20190320' ), ( 'EXECUTED', 'Y' ))
       )
 WHERE num_cond = 2
;



        DI      FK_ID YEK      ELV     
---------- ---------- -------- --------
         1         10 DATE     20190320
         2         10 EXECUTED Y



yet another approach

Racer I., April 01, 2019 - 1:36 pm UTC

Hi,

Also (if there are not more complex cases) :
SELECT
  a1.fk_id
FROM abc a1
  JOIN abc a2 ON (a1.fk_id = a2.fk_id AND a1.yek = 'DATE' and a2.yek = 'EXECUTED')
WHERE (a1.ELV, a2.elv) IN (('20190320','Y'))


regards,

Chris Saxon
April 02, 2019 - 10:30 am UTC

Good stuff.

single scan

Racer I., April 05, 2019 - 12:07 pm UTC

Hi,

Scanning the table only once (relies on E after D though):

WITH
CQ AS (
  SELECT fk_id, COUNT(*) Cnt,
    MAX(elv) KEEP (DENSE_RANK FIRST ORDER BY yek) elv1, MAX(elv) KEEP (DENSE_RANK LAST ORDER BY yek) elv2
  FROM   abc
  WHERE   yek IN ('DATE', 'EXECUTED')
  GROUP BY fk_id)
SELECT fk_id
from  CQ    
WHERE (ELV1, elv2) IN (('20190320','Y'))
AND CNT = 2


Actually this post is for an additional question :
Can Oracle optimize a HASH JOIN of a table to itself to use only a single FULL SCAN somehow? Woudl this be logically possible?

regards,
Chris Saxon
April 08, 2019 - 11:11 am UTC

Nice - though it only shows one row in the output. The OP may want to unpivot this at the end.

On hash joining a table to itself - there are all sorts of optimizations which can happen under the hood to reduce table accesses. This is largely hidden from you though.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.