I have two tables which have a common key between them, and quite a lot of other important infos ; for the sake of simplicity i will be using Combination A and Combination B. When a combination is met, whichever table has the maximum number of records should be the source where i collect the information ; in this case say IDs. The priority when counts are same is Table1.
COMMONKEY column is the combination/join condition in my tables.
(Table 1)
SELECT '123' table1_id,'Comb A' commonkey from dual UNION
SELECT '124' table1_id,'Comb A' commonkey from dual UNION
SELECT '125' table1_id,'Comb A' commonkey from dual UNION
SELECT '126' table1_id,'Comb A' commonkey from dual UNION
SELECT '215' table1_id,'Comb B' commonkey from dual UNION
SELECT '216' table1_id,'Comb B' commonkey from dual UNION
SELECT '559' table1_id,'Random Combination 1' commonkey from dual UNION
SELECT '560' table1_id,'Random Combination 2' commonkey from dual ;
( Table 2 )
SELECT 'abc1' table2_id,'Comb A' commonkey from dual UNION
SELECT 'abc2' table2_id,'Comb A' commonkey from dual UNION
SELECT 'abc3' table2_id,'Comb A' commonkey from dual UNION
SELECT 'abc4' table2_id,'Comb A' commonkey from dual UNION
SELECT 'xyz1' table2_id,'Comb B' commonkey from dual UNION
SELECT 'xyz2' table2_id,'Comb B' commonkey from dual UNION
SELECT 'xyz3' table2_id,'Comb B' commonkey from dual UNION
SELECT 'xyz2' table2_id,'Comb B' commonkey from dual UNION
SELECT '416abc1' table2_id,'Random Combination 91' commonkey from dual UNION
SELECT '416abc2' table2_id,'Random Combination 92' commonkey from dual;
Result Set Expected :
ID COMMONKEY
123 Comb A
124 Comb A
125 Comb A
126 Comb A
xyz1 Comb B
xyz2 Comb B
xyz3 Comb B
559 Random Combination 1
560 Random Combination 1
416abc1 Random Combination 91
416abc2 Random Combination 92
( the image shows a screenshot of the trail data in an excel; The Requirement and Strategy are color mapped to make it quickly understandable )
I need to generate the result set using SQL as follows :
When table1.commonkey = table2.commonkey hits, I need to-
- If table1 has 10 IDs, table2 has 5 IDs -> Pick 10 IDs from table1.
- If table1 has 15 IDs, table2 has 30 IDs -> Pick 30 IDs from table2.
- If table1 has 4 IDs, table2 has 4 IDs -> Pick 4 IDs from table1. ( when equal, choose table1 IDs )
- When no matches occur with the common key, prevent a cross join and add in the rowsets linearly to the result table.
Edit : I've initially gone on routes with
a left join b where b.key IS null ;
a full outer join b where b.key IS NULL or a.key is NULL ;
to achieve workarounds with A-B or B-A result sets but both these approaches were quite wrong. Gathering Delta sets or Exclusion sets didnt go well.
