Skip to Main Content
  • Questions
  • Select from a table where a key value has matching link for all key values of another table

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Don.

Asked: February 18, 2019 - 11:24 pm UTC

Last updated: February 19, 2019 - 11:16 am UTC

Version: 12c

Viewed 1000+ times

You Asked

I have a set of three tables (T1, T2, T3) that represent dictionary data from external sources. I need to match the data from table 1 with the data in table 3 where all the rows in table 1 for a given OR_ID are reflected in table 3. There is a second table that has a crosswalk between the attributes (CD_ID, R_ID2) of tables 1 and 3. For example, table 1 might have a key (fruit) with two rows (apple, banana) and table 3 has a key (color) with 2 rows (red, yellow). Table 2 has two rows (apple, red) and (banana, yellow). Table 1 has another key (grain) with two rows (wheat, rye) and table 3 has a key (powder) with one row (flour). Table 2 has two rows (wheat, flour) and (rye, whiskey). The following query correctly returns fruit and not grain. It seems overly complex and I haven't come up with something simpler.

with o as (
    select T1.OR_ID, T1.CD_ID, T2.R_ID, 
        count(*) over (partition by T1.OR_ID) as CNT  
    from T1 
    join T2 
        on T2.CD_ID2 = T1.CD_ID
    ), 
x as (
    select o.OR_ID, o.CNT, T3.L_ID, count(*) as CNT_T3 
    from o 
    join T3 
        on T3.R_ID2 = o.R_ID 
    group by o.OR_ID, o.CNT, T3.L_ID 
    )
select x.OR_ID, x.CNT, x.L_ID, x.CNT_T3 
from x 
    where CNT = CNT_T3 
order by x.OR_ID, x.L_ID; 


To continue the example, if table 3 has extra rows, but still matches all rows in table 1, it is still selected.

Can you see way to simplify this?

Thanx in advance,
Don Simpson


with LiveSQL Test Case:

and Chris said...

You have a form of relational division here. One way to approach this is:

- Outer join all the tables, starting with t1
- Count the number of distinct t1 attributes you have per t1 or_id
- Count the number of distinct t3 attributes you have per t1 or_id and t3 l_id
- Check the second count >= the first

Which gives a query like:

with rws as (
  select * 
  from   t1
  left   join t2
  on     t1.cd_id = t2.cd_id2
  left   join t3
  on     t2.r_id = t3.r_id2
), counts as (
  select r.*,
         count ( distinct cd_id ) over ( 
           partition by or_id 
         ) t1#,
         count ( distinct r_id2 ) over (
           partition by l_id, or_id 
         ) t3#
  from   rws r
)
  select * from counts
  where  t3# >= t1#
  order  by or_id, l_id;


You can read more about this at:

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9534489800346122396

Rating

  (1 rating)

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

Comments

Don Simpson, February 19, 2019 - 3:11 pm UTC

That's excellent, thanx!

More to Explore

Analytics

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