SQL query merge two columns from two tables in one column of 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 as output

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

How to merge this?

I can not use classic LEFT OUTER JOIN.

Total number in output table for one id should be the summary of T2+T3 (FOR ID=1 2+1=3) but for ID=2 it also should exist in table output with blank second column.

Answer 1

You can simply combine the results of the two tables specifically T2 and T3 using union inside a subquery the later join it with T1 using LEFT JOIN. Try this,

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

Additionally, you can use COALESCE if you want to customize the columns having 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