Query for props list with or without values
- by vitto
Hi, I'm trying to make a SELECT on three relational tables like these ones:
table_materials
-> material_id
- material_name
table_props
-> prop_id
- prop_name
table_materials_props
- row_id
-> material_id
-> prop_id
- prop_value
On my page, I'd like to get a result like this one but i have some problem with the query:
material prop A prop B prop C prop D prop E
wood 350 NULL NULL 84 16
iron NULL 17 NULL NULL 201
copper 548 285 99 NULL NULL
so the query should return something like:
material prop_name prop_value
wood prop A 350
wood prop B NULL
wood prop C NULL
wood prop D 84
wood prop E 16
// and go on with others rows
i thought to use something like:
SELECT *
FROM table_materials AS m
INNER JOIN table_materials_props AS mp
ON m.material_id = mp.material_id
INNER JOIN table_materials_props AS p
ON mp.prop_id = p.prop_id
ORDER BY p.prop_name
the problem is the query doesn't return the NULL values, and I need the same prop order for all the materials regardless of prop values are NULL or not
I hope this example is clear!
