Tables join using mapping table

Question

I have 3 tables:

table1: col1(id), col2(segment), col3(sector), col4(year)

mapping table2: col1(segment1) => values are the same as from table1.col2, col2(segmnet2) =>values are the same as from table3.col2

table3: col1(id), col2(segment), col3(sector), col4(year)

Now, Im doing FULL OUTER JOIN:

select t1.id, t3.id
from table1 t1
full outer join table3 t3 on
t1.year = t3.year and....

But I also need to join by COL2 - SEGMENT, with using mapping table. How to do correctly do it?

sagi sagi · Accepted Answer · 2016-03-14T15:58:08

If I understood you correctly, you just need to add another full outer join:

select t1.id, t3.id
from table1 t1
full outer join mapping t2 on( t1.col2= t2.col1)
full outer join table3 t3 on(t1.year = t3.year and t2.col2 = t3.col2

Just to make sure - a full outer join keeps all the records from both tables being joined, no matter if there is a match or not! I've added another full outer join but change it to the kind of join you need if it isn't full.

Tables join using mapping table

1 Answers