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

Question

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?

Answer 1

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.