I am sorry if this question has already been asked but what I have searched so far has not answered my question so I am going to do my best to describe my issue and if it is possible to do this in a single statement.
I have 3 tables
Assets Table
+----------------+-------------+
| ID | Name |
+----------------+-------------+
| 1 | asset1 |
| 2 | asset2 |
| 3 | asset3 |
| 4 | asset4 |
+----------------+-------------+
Custom Fields Label Table
+----------------+-------------+
| ID | data |
+----------------+-------------+
| 1 | Label1 |
| 2 | Label2 |
| 3 | Label3 |
| 4 | Label4 |
+----------------+-------------+
Custom Fields Data Table
+----------------+-------------+-------------+-------------+
| ID | asset_id | input_id | data |
+----------------+-------------+-------------+-------------+
| 1 | 1 | 1 | This |
| 2 | 1 | 2 | That |
| 3 | 2 | 1 | Other |
| 4 | 4 | 4 | Thing |
+----------------+-------------+-------------+-------------+
Ok, I feel like I am really taking a stretch here and out of my mind, but let me explain.
I have an Assets Table where customers can enter their different assets. I also have the Custom Fields Label Table where I created the ability for the customer to create custom input fields that they can add to the asset in case the default fields I have are not enough. For instance, I have 2 fields in my assets called Name and Number, but let's say the customer needs 4 more fields called Label1, Labe2, Label3, and Label4. They can add that.
Once a customer enters some data into one of their custom fields then that data is saved in the Custom Fields Data Table so there can be multiple records for each custom field depending on how many custom fields they saved data in for each asset.
From the final result below as you can see I would like that the column titles are from the Custom Fields Label Table and all of that is displayed in a single row.
If it is impossible to have the column name come from data in a record I can live with that, what is more important is getting all the data into a single record.
I am pretty sure I am asking the impossible, but I have to ask. Is this even possible?. I was messing with all kinds of joins but nothing was sticking.
Final Result
+----------------+-------------+-------------+-------------+-------------+-------------+
| ID | Name | Label1 | Label2 | Label3 | Label4 |
+----------------+-------------+-------------+-------------+-------------+-------------+
| 1 | asset1 | This | That | *null* | *null* |
| 2 | asset2 | Other | *null* | *null* | *null* |
| 3 | asset3 | *null* | *null* | *null* | *null* |
| 4 | asset4 | *null* | *null* | *null* | Thing |
+----------------+-------------+-------------+-------------+-------------+-------------+
The final result is best done at the application level. On the database server side, only a fixed number of columns can be retrieved, like this:
SELECT
a.*,
d1.data AS Label1,
d2.data AS Label2,
d3.data AS Label3,
d4.data AS Label4
FROM assets a
LEFT JOIN data d1 ON d1.asset_id = a.ID AND d1.input_id = 1
LEFT JOIN data d2 ON d2.asset_id = a.ID AND d2.input_id = 2
LEFT JOIN data d3 ON d3.asset_id = a.ID AND d3.input_id = 3
LEFT JOIN data d4 ON d4.asset_id = a.ID AND d4.input_id = 4
You can use prepared statements to get a dynamic number of columns based on the label table.
SELECT
GROUP_CONCAT(CONCAT('d', l.ID, '.data AS ', l.Name )) AS select_clause,
GROUP_CONCAT(CONCAT(
'LEFT JOIN data d', l.ID,
' ON d', l.ID, '.asset_id = a.ID AND d', l.ID, '.input_id = ', l.ID
) SEPARATOR ' ') AS join_clause
INTO @selectClause, @joinClause
FROM labels l;
SET @sql = CONCAT(
'SELECT a.*, ', @selectClause,
' FROM assets a ', @joinClause,
' ORDER BY a.ID'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;