Geek Answers Handbook

SQL select merges 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 be obtained from table C or table D.

Now I need a query that shows the ID from table A and a second column named Name , which consists of the corresponding names based on column B_ID table B.

The expected result should look like this:

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

I tried this query:

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

This gives:

 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 problems:

  • I need to combine the names into one column (see the expected result above)
  • It should be a SELECT subquery based on Table A to show the column ID table A.
+6
source share
3 answers

Here is one parameter 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
+4
source

You can replace * 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 is not difficult ...

 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 processing for NULL values ​​or for processing possible values ​​of duplicate identifiers, the query can be modified. (The sample query takes unique values ​​for x_ID columns.)

+3
source

Given these tables, I suggest the following query:

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

If you think duplicate values ​​can be returned and you want to use them, use UNION ALL instead of UNION .

0
source

More articles:


All Articles