SQL select merge two columns into one

Question

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:

I need to merge the names into a single column (see the expected result above)
It needs to be a subquery of a SELECT based on Table A in order to show the ID column of Table A.

it is already added. please read. see `The expected result should look like:` — beta, Jul 25 '16 at 14:32
To modify your own query you should look at `COALESCE(C_Name, D_Name) AS Name`, but as @sgeddes has pointed out, there is a much more direct approach. — MatBailie, Jul 25 '16 at 14:36

score 4 · Accepted Answer · answered Jul 25 '16 at 14:31

4

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

answered Jul 25 '16 at 14:31

sgeddes

62,311
6
61
83

I get a hint in my example, that "(left) outer joins" should be used. do you have an intuitive solution with "(left) outer joins) ? – beta Jul 25 '16 at 14:32
1

@beta -- if you need to use an `outer join`, replace `join` with `left join`. It's only needed if not all corresponding bids would exist on those tables. – sgeddes Jul 25 '16 at 14:33

score 3 · Answer 2 · answered Jul 25 '16 at 14:38

You can replace * with expressions that do what you want

SELECT B.B_ID
     , COAELESCE(C.C_NAME,D.D_NAME) AS `Name`

And adding a join to table A isn't hard...

SELECT A.A_ID                       AS `Id`
     , COAELESCE(C.C_NAME,D.D_NAME) AS `Name`
  FROM A
  LEFT
  JOIN B
    ON B.B_ID = A.B_ID
  LEFT
  JOIN C
    ON C.C_ID = B.B_ID
  LEFT
  JOIN D
    ON D.D_ID = B.B_ID
 ORDER
    BY A.A_ID

If you need different handling for NULL values, or for handling potential duplicate ID values, the query can be tweaked. (The example query assumes unique values for the x_ID columns.)

score 0 · Answer 3 · answered Jul 25 '16 at 14:45

Given the tables as defined, I suggest the following query:

SELECT A.ID, C.C_NAME
FROM @A A INNER JOIN @C C ON A.B_ID=C.C_ID
UNION
SELECT A.ID, D.D_NAME
FROM @A A INNER JOIN @D D ON A.B_ID=D.D_ID

If you think there could be duplicate values returned and you want them, use UNION ALL instead of UNION.

SQL select merge two columns into one

3 Answers3