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
part_category_name

tbl_parts (contains part details)
part_id - unique
part_name
part_category_id

tbl_required_parts (contains a list of parts required for a given assembly type)
assembly_type - contains duplicates for each part category required
part_category_id

tbl_assemblies (contains details for given assembly)
assembly_id - unique
assembly_name

tbl_assemblies (links parts to assemblies)
assembly_link_id - contains duplicates for every part linked to id
part_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.
For example-

Code:
part_category_name | part_name
     Category 1    |   Part 1
     Category 1    |   Part 2
     Category 2    |   NULL
     Category 3    |   Part 6