2

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?

3
  • EAV schema is a hassle.
    – Rick James
    Nov 12 '16 at 4:53
  • Yeah, I've read that EAV only has a limited number of scenarios where it is suitable. But since my current structure is not working at all and I am missing the experience with database design I have to test this to understand the problems and flaws with it. Altough I fear that "testing" will become a permanent solution...
    – hamena314
    Nov 12 '16 at 8:10
  • Mar 23 '17 ... The testing version is still "temporary"...
    – hamena314
    Mar 25 '17 at 10:33
4

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.