SQL Query Help

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