Mysql – How to fill in and rename columns in a 2-table join

database-designjoin;MySQL

I am working on creating a database for machine parts, which will be used by a Java program for further processing.

I have 2 tables, one for the orders and one for the components:

orders-table:

+----+------------------+-------+--------+---------+---------------+-------+
|    |                  |       |        |         |               |       |
| ID | equipment_number | brake | buffer | limiter | smokeDetector | (...) |
|    |                  |       |        |         |               |       |
+--------------------------------------------------------------------------+
| 1  | 1111             |   1   |   2    |   3     |     4         |       |
| 2  | 2222             |   98  |   2    |   96    |     99        |       |
| 3  | 3333             |   1   |   105  |   205   |     104       |       |
| 4  | 4444             |   102 |   117  |   147   |     200       |       |
+----+------------------+-------+--------+---------+---------------+-------+

compslist-table:

+----+------------------+----------------+-------------+-----+
|    |                  |                |             |     |
| ID | type             | manufacturer   | certificate | (..)|
|    |                  |                |             |     |
+------------------------------------------------------------+
| 1  | brake            | bosch          | xyz         |     |
| 2  | buffer           | bosch          | 123         |     |
| 3  | limiter          | mercedes       | abc         |     |
| 4  | smokeDetector    | mercedes       | efg         |     |
+----+------------------+----------------+-------------+-----+

Now I want to join those 2 tables together and "expand" the columns "brake, buffer, limiter, smokeDetector" in the orders-table, by "filling in" the information from the columns of the compslist-table.

So the result I want is this:

Wanted result:

+----+------------------+-----------------+--------------------+-------------------+-------------+---------------------+--------------------+-------+
|    |                  |                 |                    |                   |             |                     |                    |       |
| ID | equipment_number | brake_type      | brake_manufacturer | brake_certificate | buffer_type | buffer_manufacturer | buffer_certificate | (...) |
|    |                  |                 |                    |                   |             |                     |                    |       |
+---------------------------------------------------------------------------------------------------------------------------------------------------+
| 1  | 1111             | brake           | bosch              | xyz               | buffer      | bosch               | 123                |       |
| 2  | 2222             | brake           | bmw                | zut               | buffer      | bosch               | 123                |       |
| 3  | 3333             | brake           | bosch              | xyz               | buffer      | toyota              | ert                |       |
| 4  | 4444             | brake           | mercedes           | gbn               | buffer      | mercedes            | dfg                |       |
+----+------------------+-----------------+--------------------+-------------------+-------------+---------------------+--------------------+-------+

I've tried to accomplish this by multiple joins for the components (brake, buffer, …): 

SELECT * FROM orders JOIN compslist AS A ON orders.brake = A.compslist_id 
    JOIN compslist AS B ON orders.buffer = B.compslist_id
    JOIN compslist AS C ON orders.limiter = C.compslist_id
    JOIN compslist AS D ON orders.smokeDetector = D.compslist_id;

But this leads to a result, where I get ALL columns from EVERY table for every component:

Result I get at the moment:

+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|order_id   | equipment_number   | brake   | buffer   | limiter   | smokeDetector   | compslist_id   | type        | manufacturer   | certificate   | compslist_id   | type       | manufacturer   | certificate   | compslist_id   | type          | manufacturer   | certificate       | compslist_id   | type           |  manufacturer |    certificate |                    
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|       2   |444                 |  1      |    2     | 3         |     4           |       1        |   brake     | bosch          |   xyz         |   2            | buffer     | bosch          |    123        |    3           |   limiter     |  vw            |   abc             |  4             | smokeDetector  |   vw          |        efg     |                                          
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

This contains the information I want, but is way longer than I need. Also now I have several columns named "type", "manufacturer" etc., which makes it harder to grab the columns in Java by their name.

So is there a way to "fill in" the information from the second table into the first table, while creating new names for the columns?

Best Answer

Uhm ... I've found the answer myself. After Roman Hocke reminded me, that you can use alias for the columns as well:

SELECT O.order_id, O.equipment_number, 
    C.type AS brake_type, 
    C.manufacturer AS brake_manufactureer, 
    C.certificate AS brake_certificate,
    C2.type AS buffer_type,
    C2.manufacturer AS buffer_manufacturer,
    C2.certificate AS buffer_certificate
    FROM orders AS O 
    INNER JOIN compslist AS C 
        ON O.brake = C.compslist_id
    INNER JOIN compslist AS C2
        ON O.buffer = C2.compslist_id;

This gives me exactly the result I was looking for:

+----+------------------+-----------------+--------------------+-------------------+-------------+---------------------+--------------------+-------+
|    |                  |                 |                    |                   |             |                     |                    |       |
| ID | equipment_number | brake_type      | brake_manufacturer | brake_certificate | buffer_type | buffer_manufacturer | buffer_certificate | (...) |
|    |                  |                 |                    |                   |             |                     |                    |       |
+---------------------------------------------------------------------------------------------------------------------------------------------------+
| 1  | 1111             | brake           | bosch              | xyz               | buffer      | bosch               | 123                |       |
| 2  | 2222             | brake           | bmw                | zut               | buffer      | bosch               | 123                |       |
| 3  | 3333             | brake           | bosch              | xyz               | buffer      | toyota              | ert                |       |
| 4  | 4444             | brake           | mercedes           | gbn               | buffer      | mercedes            | dfg                |       |
+----+------------------+-----------------+--------------------+-------------------+-------------+---------------------+--------------------+-------+

For the sake of brevity I've omitted the other components like the limiter and the smokeDetector, but the approach is the same for both components.

Related Question