I need help with generating a query to pull from a table called "Mat_Req" to pull a multi-level BOM. When I type in the job# in my query, it would pull both purchased and fabricated parts. I am only interested in the sub component purchased materials to make these fabricated parts. So my main query, I had filtered down to only pulling fabricated items.
Here's the query I am using:
SELECT JOB_ID, PARENT_ID, TYPE, QTY, COMPONENT_ID, COMPONENT_TYPE, COMPONENT_QTY
WHERE TYPE = 'FAB' AND JOB_ID = '12454'
The above query would pull the highlighted items in Green as per the image below. The item in green are all linked to job 12454, but the items not highlighted are different jobs to make that fabricated part. I need help to pull those items.
Raw Table Data. Noticed that line 7 and 8, Parent ID aaa10 require 2 materials to make that one. First one was a purchased item and second is a fabricated item. That fabricated item has a purchased raw material to make it so I also need to drill down. Some jobs can have as deep as 5 or so multi-levels.
This is my final output. Only the purchased materials from the sub components when I put in the main Job#: