sql subquery joins

Question

I am trying to allow for the custom creation of tables and columns using a few tables in the database.

The Data is in a table called CustomData,

ID | CustomEntries_ID | CustomColumn_ID | Value
-------------------------------------------------
1  |       1          |        1        | Pheven
2  |       1          |        2        | Routine
3  |       1          |        3        | Planned
4  |       1          |        4        | 2014
5  |       2          |        1        | John
6  |       2          |        2        | Routine
7  |       2          |        3        | Planned
8  |       2          |        4        | 2017


SELECT * FROM (
    (SELECT CustomEntries_ID AS ID, `Value` AS `Name` FROM `CustomData` WHERE CustomColumn_ID = 1) AS T1 NATURAL JOIN
    (SELECT CustomEntries_ID AS ID, `Value` AS `Servive` FROM `CustomData` WHERE CustomColumn_ID = 2) AS T2 NATURAL JOIN
    (SELECT CustomEntries_ID AS ID, `Value` AS `Type` FROM `CustomData` WHERE CustomColumn_ID = 3) AS T3 NATURAL JOIN
    (SELECT CustomEntries_ID AS ID, `Value` AS `Model` FROM `CustomData` WHERE CustomColumn_ID = 4) AS T4 NATURAL JOIN
    )

The first problem seems to be if there is an entry missing the CustomData table all data relating to that customEntries_ID are excluded. The second issue is this seems very inefficient, I'm sure someone has a better suggestion for this query or to allow for Custom table creation without actually creating new tables on the database.

Answer 1

Just use conditional aggregation:

SELECT customernetyid
       max(case when CustomColumn_ID = 1 then value end) as name,
       max(case when CustomColumn_ID = 2 then value end) as service,
       max(case when CustomColumn_ID = 3 then value end) as type,
       max(case when CustomColumn_ID = 4 then value end) as model
FROM CustomData
GROUP BY customentryid;