SQL query joining two columns from two tables into one column of the resulting table?

Question

I have the following tables:

T1

ID 1 2 3 

T2

 ID SERVICE 1 PSTN 1 ADSL 3 ADSL 

T3

 ID DEV 1 3G 3 2G 

I want to output

 ID SERVICE/DEV 1 PSTN 1 ADSL 1 3G 2 3 ADSL 3 2G 

How to merge it?

I can not use the classic LEFT OUTER JOIN .

The total number in the output table for one identifier should be the sum of T2+T3 (FOR ID=1 2+1=3) , but for ID=2 it should also exist in the output of the table with an empty second column.

Answer 1

You can simply combine the results of two tables, in particular T2 and T3 , using union inside the subquery, and then join it to T1 using LEFT JOIN . Try it,

 SELECT t1.ID, b.Service FROM T1 LEFT JOIN ( SELECT ID, Service FROM T2 UNION ALL SELECT ID, Dev AS Service FROM T3 ) b ON t1.ID = b.ID 

Alternatively, you can use COALESCE if you want to customize columns with null values. So in the example below, since 2 has no service, it will show -none- instead of null

 SELECT t1.ID, COALESCE(b.Service, '-none-') Service FROM T1 LEFT JOIN ( SELECT ID, Service FROM T2 UNION ALL SELECT ID, Dev AS Service FROM T3 ) b ON t1.ID = b.ID 

See SQLFiddle Demo

Answer 2

Do you want to merge / merge everything?

 select * from ((select id, service from t2 ) union all (select id, service from t3 ) union all (select id, NULL as service from t1 where t1.id not in (select id from t2) and t1.id not in (select id from t3) ) ) t 

NOT IN in a WHERE may not be the most efficient way to do this. But is this the result you are striving for?

Answer 3

You can try this.

 SELECT T1.ID, service "SERVICE/DEV" FROM T1, T2 WHERE T1.ID = T2.ID(+) UNION SELECT T1.ID, dev "service/dev" FROM T1, T3 WHERE T1.ID = T3.ID(+);