Item Variant Report with Filters?

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.

Never Mind found a solution to this problem on stack overflow here is the link:

Turns out I needed to join the tabItem to the tabItem Variant Attribute table multiple times. Here is the sample code for future reference:

SELECT
	it.name as "Name::100",
	it.description as "Description::200",
	bm.attribute_value as "BM::100",
	tt.attribute_value as "TT::100"

FROM `tabItem` it
LEFT JOIN `tabItem Variant Attribute` bm 
	ON it.name = bm.parent 
	AND bm.attribute = 'Base Material'
LEFT JOIN `tabItem Variant Attribute` tt
	ON it.name = tt.parent 
	AND tt.attribute = 'Tool Type'

WHERE bm.attribute_value = 'Carbide'
	AND tt.attribute_value = 'Drill'