I was hoping someone could help me with a SQL query. I am running MS SQL Server 2008. I've tried to explain what I'm trying to do. I don't know if I explain well enough to make sense.
Thanks.
For each mw_serialassembly , We need to loop thru the mw_serialnumbers table using the serialnumbers_attachedid and list out the Bill of Materials (BOM) for this assembly.
So for instance, the DB structure looks like this:
serialnumbers_id serialnumbers_attachedid
31066 15343
31066 16106
31066 31071
31070 16049
31070 31082
31070 31088
For serialnumbers_id, there will be an entry for the completed assembly item with the serialnumbers_attachedid being the “components” of the BOM. We need to look at the mw_serialnumbers table for each ID and list out the serialnumbers_no for that ID, the item_id for that serialnumbers_no , and then do the same for each “attached_id” so that we have something that looks like this:
Item_id serialnumbers_no (assembly SN), item_id (attached id/component) serialnujmbers_no (attached id/component)
10246 HD0000062 10249 NLS000123
10246 HD0000062 10250 ENC000123
Open in new window
This give results like this which I think is close to what you want