Hi,
I am trying to write a report for the item variants to show the different attributes in different columns with the option to filter out the results based on the Variant filter selected but I am stuck at the mysql query and I cannot seem to find a way.
Though I have been able to show the attributes of an item in columns, I am unable to apply the filters since my query is based on sub-query. Could any one please help me with the mysql query where I could also filter out the results based on attribute_values:
Here is my query:
SELECT
it.name as "ID:Link/Item:100",
it.description as "Description::100",
(SELECT
iva.attribute_value
FROM `tabItem Variant Attribute` iva
WHERE iva.parent = it.name
AND iva.attribute = 'Base Material'
) as "BM::100",
(SELECT
iva.attribute_value
FROM `tabItem Variant Attribute` iva
WHERE iva.parent = it.name
AND iva.attribute = 'Brand'
) as "Brand::100",
(SELECT
iva.attribute_value
FROM `tabItem Variant Attribute` iva
WHERE iva.parent = it.name
AND iva.attribute = 'Special Treatment'
) as "SPL::100",
(SELECT
iva.attribute_value
FROM `tabItem Variant Attribute` iva
WHERE iva.parent = it.name
AND iva.attribute = 'Tool Type'
) as "TT::100",
(SELECT
iva.attribute_value
FROM `tabItem Variant Attribute` iva
WHERE iva.parent = it.name
AND iva.attribute = 'd1_mm'
) as "d1_mm:Float:100"
FROM `tabItem` it
The problem with sub-query is that if I want to filter out the results like
if iva.attribute_value = 'HSS' for iva.attribute = 'Base Material'
AND iva.attribute_value = 'ABC' for iva.attribute = 'Special Treatment'
then the above filters would not work as the query is based on sub-query and the filters would not apply on the item tables, hence I think I need to resolve this by using joins but they tend to fail if I have more than one attribute value condition.