Build Query for Parts Hierarchy

--- Simple query showing list of parts --- select part_no ,description from part; --- Show parts and thier composition --- select part.part_no , part.description , assembly.assembly_part_no , assembly.quantity from part join assembly on assembly.base_part_no = part.part_no; --- Wait, where did the top level part go? --- --- We lost it because of the join. Why? --- --- bring back in the top level part with an outer join --- select part.part_no , part.description , assembly.assembly_part_no , assembly.quantity from part Left join assembly on assembly.base_part_no = part.part_no; --- rename the part table in preparation for the next step --- select base_part.part_no , base_part.description , assembly.assembly_part_no , assembly.quantity from part base_part left join assembly on assembly.base_part_no = base_part.part_no; --- Bring in the name of of the assembled part --- --- This requires us to join to the part table a second time, requiring aliases on the part table --- select base_part.part_no , base_part.description , assembly.assembly_part_no , assembly.quantity , assembled_part.description as assembled_part_description from part base_part left join assembly on assembly.base_part_no = base_part.part_no left join part assembled_part on assembled_part.part_no = assembly.assembly_part_no;