[solved] Help Required in SQL Query

Frappe Framework Custom Script
1

I am trying to create a SQL Report on BOM and I want:
Item Code, Item Name, Item Group, BOM No, Qty, Stock UOM

SELECT
b.item_code as “Item Code:Link/Item:60”,
b.item_name as “Item Name::120”,
i.item_group as “Item Group:Link/Item Group:220”,
b.bom_no as “BOM No::120”,
b.qty as “Quantity::60”,
b.stock_uom as “Stock UOM::100”
FROM tabBOM Item b,tabItem i,tabBOM bom
WHERE b.bom_no=bom.item
AND i.item_code=b.item_code

But I am not able to get the parent Item code in the query,
can anybody help me to create a join for the same.

2

Try with this, hope it will help you.

WHERE b.parent=bom.name
AND i.name=b.item_code

3

I have already done it, thanks, no need to include the BOM table, here is my query:

SELECT
b.item_code as “Item Code:Link/Item:120”,
b.item_name as “Item Name::220”,
i.item_group as “Item Group:Link/Item Group:220”,
b.parent as “Parent:Link/BOM:200”,
b.qty as “Quantity::60”,
b.stock_uom as “Stock UOM::100”
FROM tabBOM Item b,tabItem i
WHERE i.item_code=b.item_code
ORDER BY b.parent