I have the following shcema :
product (id, name, price)
property (id, name, value, product_id)
Products may have, or not, some properties.
If I want to study "Material" and "Color" properties, the corresponding SQL is :
SELECT ALL p.id, p.name, p.price, p_material.value AS material, p_color.value AS color FROM product AS p LEFT OUTER JOIN property AS p_material ON p.id = p_material.product_id AND p_material.name = 'Material' LEFT OUTER JOIN property AS p_color ON p.id = p_color.product_id AND p_color.name = 'Color'
How can I make this query with the Metabase editor ?
We can't put directly literals in
ON criterias (nice feature incoming ?), so I made custom expressions.
I can't make a custom expression like
= "Material" or
so I named a custom column
Literal "Material" containing
= concat("Material", "")
and another one named
Literal "Color" containing
= concat("Color", "").
These custom expressions can be inserted in
That seems tricky. Is it the normal way to do that ?