Query data from 3 tables based on values of one common column

Question

I have 3 tables of the format

T1: T1_ID, Reference, ...
T2: T2_ID, Reference, ...
T3: T3_ID, Reference, ...

I want to print the output as a table consisting of 3 columns T1_ID, T2_ID an T3_ID showing the ID's based on the values of the column 'Reference' which are common to all the three tables.

Welcome to Stack Overflow. You will have a better experience here if you take the Tour and read through How To Ask, then write your question with the details needed to create a Minimal, Reproducible Example. Also, include a question in your question — Spyros K, Jun 30 '20 at 14:37

score 0 · Answer 1 · answered Jun 30 '20 at 14:20

Is this what you want?

select reference as t1_id, null as t2_id, null as t3_id
from t1
union all
select null as t1_id, reference as t2_id, null as t3_id
from t2
union all
select null as t1_id, null as t2_id, reference as t3_id
from t3;

This doesn't seem particularly useful. But that is how I am reading the question.

Or do you want a full join:

select t1.t1_id, t2.t2_id, t3.t3_id
from t1 full join
     t2
     using (reference) full join
     t3
     using (reference)

Roberto Hernandez · Answer 2 · 2020-07-01T09:05:11.123

As you are not providing so much information regarding how to join the three tables, I will guess you want an INNER JOIN, so only the records that match the join criteria will be shown.

SQL> SELECT t1.t1_id, t2.t2_id, t3.t3_id
     FROM t1 
     inner  join t2 on ( nvl(t1.reference,'XX') = nvl(t2.reference,'XX') )
     inner  join t3 on ( nvl(t1.reference,'XX') = nvl(t3.reference,'XX') )

I am using INNER JOIN to get only the records that match the criteria where reference is the same in the three tables. To avoid nulls, which I don't know are possible as you don't say anything regarding that, I use NVL to avoid a problem with those. Keep in mind the differences between a FULL JOIN or a INNER JOIN. For that, please read the following article:

Inner Join vs Full join

Query data from 3 tables based on values of one common column

2 Answers2