8bitreboot 8bitreboot - 1 month ago 12
MySQL Question

Modify/flatten result from sql one to many query with multiple joins

Consider the following simplified schema example -

sys_systems

id name
---------------------
1 Inventory


sys_shadow_data

id primary_key_value sys_schema_id field_value
---------------------------------------------------------
1 1 143 Awaiting audit
2 1 144 90


sys_schemas

id field_name
-------------------
143 notes
144 status


The following query -

select sys_systems.*,
(select sys_shadow_data.field_value where sys_schemas.field_name = 'status') as status,
(select sys_shadow_data.field_value where sys_schemas.field_name = 'notes') as notes
from sys_systems
left join sys_shadow_data
on sys_systems.id = sys_shadow_data.primary_key_value
left join sys_schemas
on sys_schemas.id = sys_shadow_data.sys_schema_id


yields -

id name status notes
-----------------------------------------
1 Inventory 90 null
1 Inventory null Awaiting audit


The result I would like to obtain is -

id name status notes
-----------------------------------------
1 Inventory 90 Awaiting audit


Is there a change I can make to the sql query to achieve this or is this a case whereby I would need to process the results in the application code?

Answer

Your query amounts to pivoting the joined tables on the field_name column from the sys_schemas table to generate new columns for each value in that column. You can GROUP BY the inventory ID and name, then use MAX to collapse the two records into one:

SELECT sys_systems.id,
       sys_systems.name,
       MAX(CASE WHEN sys_schemas.field_name = 'status'
                THEN sys_shadow_data.field_value ELSE NULL END) AS status, 
       MAX(CASE WHEN sys_schemas.field_name = 'notes'
                THEN sys_shadow_data.field_value ELSE NULL END) AS notes 
FROM sys_systems
LEFT JOIN sys_shadow_data
    ON sys_systems.id = sys_shadow_data.primary_key_value
LEFT JOIN sys_schemas
    ON sys_schemas.id = sys_shadow_data.sys_schema_id
GROUP BY sys_systems.id,
         sys_systems.name
Comments