beta beta - 4 months ago 7
SQL Question

SQL select merge two columns into one

I have four tables:

Table A:

ID | B_ID
----------
1 | 5
2 | 6
3 | 7
4 | 8


Table B:

B_ID
-----
5
6
7
8


Table C:

C_ID | C_Name
--------------
5 | Alpha
6 | Beta


Table D:

D_ID | D_Name
--------------
7 | Delta
8 | Gamma


Note, that the values in Table B can come from either Table C or Table D.

I now need a query which shows
ID
from Table A and a second column called
Name
which consists of the corresponding names based on the
B_ID
column of Table B.

The expected result should look like:

ID | Name
----------
1 | Alpha
2 | Beta
3 | Delta
4 | Gamma


What I tried is this query:

SELECT *
FROM B
LEFT OUTER JOIN C
ON B_ID = C_ID
LEFT OUTER JOIN D
ON B_ID = D_ID


This yields:

B_ID | C_ID | C_Name | D_ID | D_Name
-------------------------------------
5 | 5 | Alpha | Null | Null
6 | 6 | Beta | Null | Null
7 | Null | Null | Null | Delta
8 | Null | Null | Null | Gamma


However, I still have two issues:


  1. I need to merge the names into a single column (see the expected
    result above)

  2. It needs to be a subquery of a
    SELECT
    based on
    Table A in order to show the
    ID
    column of Table A.


Answer

Here's one option using a subquery with union all:

select a.id, b.name
from tablea a
   join (select id, name from tablec 
         union all select id, name from tabled) b on a.B_ID = b.id
Comments