Key and value lookup in relational database

Question

I have the data as follows

TABLE1
------
A  |B
1  |2
3  |4

TABLE2
------
C  |D
1  |11
2  |12
3  |13
4  |14

The expected output is

D , D
--------
11, 12
13, 14

The logic behind this output is, I need to create a view so that the view always returns me column D of table2 corresponding to column A or column B from table1 matching with column C of table2. This view will act as a lookup for finding the values in Table2 based on keys in table1. If in case, any one of the key in table1 does not have a corresponding value in table2 then the pair should not be reported.

Data Setup:

CREATE TABLE Table1
    ("A" number, "B" number);

INSERT ALL 
    INTO Table1 ("A", "B")       VALUES (1, 2)
    INTO Table1 ("A", "B")       VALUES (3, 4)
SELECT * FROM dual;

CREATE TABLE Table2
    ("C" number, "D" number);

INSERT ALL 
    INTO Table2 ("C", "D")       VALUES (1, 11)
    INTO Table2 ("C", "D")       VALUES (2, 12)
    INTO Table2 ("C", "D")       VALUES (3, 13)
    INTO Table2 ("C", "D")       VALUES (4, 14)
SELECT * FROM dual;

I tried some joins with string concatenation but they look horrible. This is actually a Kognitio database but most of the oracle syntax works here. I also referred this with no use. Also this a DWH environment and we apply this logic on surrogate keys.

Answer 1

You simply want to join twice:

select t2a.D, t2b.D
from table1 t2 join
     table2 t2a
     on t1.A = t2a.C join
     table2 t2b
     on t1.B = t2b.C;

Note the use of two different aliases for table2. This is how these are distinguished in the query.