Problem with the Query

Hi Everyone,

I am extracting data from BOM.
Below is my query for the same:

select 
distinct m.name as "BOM",
m.item_name as "Item Name",
d.bom_no as "BOM No.",
if(m.docstatus=0,"Draft","Submitted") as "Document Status" 
FROM `tabBOM` m left outer join `tabBOM Item` d on m.name=d.parent
where m.docstatus!=2;

My worry is that where there is a Sub-Assembly in a BOM the data is repeating, I want one single line of each BOM no matter whether there is a Sub-Assembly or not.

Can anyone help me how to get rid-off it?
Do, I have to use connect by prior for that?

Hello Ruchin,

It showing two records because you have added two items in sub assembly against the bom # BOM/010-001194/001. Distinct is only applicable for distinct records, here you are applying distinct on bom, item name, status and sub-assemblies bom_no. Where bom, item_name, status of both records are same but the bom_no are different(One is blank and other is BOM/680-001050/002).

Thanks, Rohit

Hi @rohit_w,
I know the problem, I appreciate if you can tell me the solution for the same.

Edit: I have checked the details it is not like this. It is happening because of the joins.
See the BOM of BOM/311-000009/001

Regards
Ruchin Sharma