How to combine two tables in Oracle SQL on a quantitative base

beacause of a really old db design I need some help. This might be quite simple I’m just not seeing the wood for the trees at the moment.

TABLE A:

ID
1
2
3
4
5

TABLE B:

ID	VALUE B
1	10
1	20
2	10
2	20
3	10
3	20
3	30
4	10

TABLE C:

ID	VALUE C
1	11
1	21
2	11
2	21
2	31
3	11
5	11

Expected result:

where ID = 1

ID	VALUE B	VALUE C
1	10	11
1	20	21

where ID = 2

ID	VALUE B	VALUE C
2	10	11
2	20	21
2	null	31

where ID = 3

ID	VALUE B	VALUE C
3	10	11
3	20	null
3	30	null

where ID = 4

ID	VALUE B	VALUE C
4	10	null

where ID = 5

ID	VALUE B	VALUE C
5	null	11

The entries in table B and C are optional and could be unlimited, the ID from table A is the connection. B and C are not directly connected. I need a quantitative comparision to find gaps in the database. The number of entries of table B and C should be the same (but not the value), usually entries are missing in either B or C.

I tried it with outer joins but I’m getting two much rows, because I need B or C join only one time per single row.

I hope anybody understand my problem and can help me.

Answer

It looks like, for each distinct ID, you want the nth row (ordered by VALUE) from TABLE_A to match with the nth row from TABLE_B. And if one table – A or B – has more values, you want those to match to null.

Your solution will have two parts. First, use row_number() over ( partition by id order by value) to order the rows in both tables. Then, use FULL OUTER JOIN to join on (id, rownumber).

Here is a full example:

-- WITH clauses are just test data...+
with table_a (id) as ( 
  SELECT 1 FROM DUAL UNION ALL
  SELECT 2 FROM DUAL UNION ALL
  SELECT 3 FROM DUAL UNION ALL
  SELECT 4 FROM DUAL UNION ALL
  SELECT 5 FROM DUAL ),
     table_b (id, value) as (
  SELECT 1,10 FROM DUAL UNION ALL
  SELECT 1,20 FROM DUAL UNION ALL
  SELECT 2,10 FROM DUAL UNION ALL
  SELECT 2,20 FROM DUAL UNION ALL
  SELECT 3,10 FROM DUAL UNION ALL
  SELECT 3,20 FROM DUAL UNION ALL
  SELECT 3,30 FROM DUAL UNION ALL
  SELECT 4,10 FROM DUAL ),
  table_c (id, value) as (
  SELECT 1,11 FROM DUAL UNION ALL
  SELECT 1,21 FROM DUAL UNION ALL
  SELECT 2,11 FROM DUAL UNION ALL
  SELECT 2,21 FROM DUAL UNION ALL
  SELECT 2,31 FROM DUAL UNION ALL
  SELECT 3,11 FROM DUAL UNION ALL
  SELECT 5,11 FROM DUAL )
  -- Solution begins here
  SELECT id, b.value b_value, c.value c_value
  FROM   ( SELECT b.*, 
                  row_number() OVER ( PARTITION BY b.id ORDER BY b.value ) rn 
           FROM table_b b ) b
  FULL OUTER JOIN ( SELECT c.*, 
                           row_number() OVER ( PARTITION BY c.id ORDER BY c.value ) rn 
                    FROM table_c c ) c USING (id, rn)
  ORDER BY id, b_value, c_value;

+----+---------+---------+
| ID | B_VALUE | C_VALUE |
+----+---------+---------+
|  1 |      10 |      11 |
|  1 |      20 |      21 |
|  2 |      10 |      11 |
|  2 |      20 |      21 |
|  2 |         |      31 |
|  3 |      10 |      11 |
|  3 |      20 |         |
|  3 |      30 |         |
|  4 |      10 |         |
|  5 |         |      11 |
+----+---------+---------+