Resultset acrosse several tables with null values?
I'm looking for some help/pointers on how to create a result set based on the following tables.
tbl_part_categories (contains categories)
part_category_id - unique
tbl_parts (contains part details)
part_id - unique
tbl_required_parts (contains a list of parts required for a given assembly type)
assembly_type - contains duplicates for each part category required
tbl_assemblies (contains details for given assembly)
assembly_id - unique
tbl_assemblies (links parts to assemblies)
assembly_link_id - contains duplicates for every part linked to id
What I'm needing is code to produce a result showing all the parts fitted to any given assembly, sorted by category, but also including null values for any required components that are missing.
part_category_name | part_name
Category 1 | Part 1
Category 1 | Part 2
Category 2 | NULL
Category 3 | Part 6