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
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