mysql order by field row display even if empty

I’ve two tables( bom and bom_parts). Need to inner join and display stock_deduct,GRN_id,part_id fields.

Table 1 (bom):

Table 2 (bom_parts):

MySql query:

SELECT 
    bom_part.stock_deduct,
    bom_part.GRN_id,
    bom.part_id 
FROM 
    bom 
    INNER JOIN 
    bom_part 
        ON bom_part.BOM_id=bom.id  
WHERE 
    batch_id='0' AND
    `sr_no`=23 
ORDER BY 
    FIELD(part_id, 34,8,36,6)

Query Result:

Expected Result:

Even if the part_id field is empty in this case 8, 6, I just want to display the part_id with the stock_deduct field as 1 in the result as mentioned below.

Stock_deduct	GRN_id	part_id
1	6	34
2	9	34
3	GRN1	34
1		8
1	GRN3	36
2	GRN2	36
1		6

As you can see the row 4 and 7 in the expected result table as the specified ORDER BY FIELD(part_id, 34,8,36,6) order.

Thanks in advance.

Answer

I think you want a left join, and filtering:

select coalesce(bp.stock_deduct, 1) as stock_deduct, bp.grn_id, b.part_id 
from bom b
left join bom_part bp on bp.bom_id = b.id  
where b.batch_id = 0 and b.sr_no = 23 and b.part_id in (34, 8, 36, 6)
order by field(part_id, 34, 8, 36, 6)

The filter on bom.part_id in the where clause brings the relevent row of bom. The we bring the parts table with a left join, so unmatched boms are not filtered out. Finally, coalesce() turns missing stock_deduct to 1.