Recursive Many-to-Many
drop table assembly;
drop table part;
create table part(
part_no int primary key,
description varchar(50)
);
insert into part values(104, 'Headlamp Lens');
insert into part values(107, 'High-beam bulb');
insert into part values(108, 'Low-beam bulb');
insert into part values(118, 'Headlamp bezel');
insert into part values(117, '20-amp fuse');
insert into part values(120, 'Fuse Cover');
insert into part values(127, 'Headlamp housing');
insert into part values(119, 'Headlamp switch');
insert into part values(34, 'Saddle cover');
insert into part values(21, 'Saddle base');
insert into part values(25, 'Small flat washer');
insert into part values(24, 'Small lock washer');
insert into part values(28, 'Bolt 1.5x5/16x20');
insert into part values(210, 'Seat');
insert into part values(215, 'Headlamp');
insert into part values(3451, 'R 1250 GS');
create table assembly(
assembly_part_no int references part,
base_part_no int references part,
quantity int,
primary key (assembly_part_no, base_part_no)
);
insert into assembly values(215,104,1);
insert into assembly values(215,107,1);
insert into assembly values(215,108,1);
insert into assembly values(215,118,1);
insert into assembly values(215,117,1);
insert into assembly values(215,120,1);
insert into assembly values(215,127,1);
insert into assembly values(215,119,1);
insert into assembly values(215,25,1);
insert into assembly values(210,34,1);
insert into assembly values(210,21,1);
insert into assembly values(210,25,2);
insert into assembly values(210,24,2);
insert into assembly values(210,28,2);
insert into assembly values(3451,210,1);
insert into assembly values(3451,215,1);
-- show all Parts --
select * from part;
-- show all parts and how they compose to make other parts --
select base_part.PART_NO
, base_part.DESCRIPTION as base_part_description
, assembly.ASSEMBLY_PART_NO as is_a_part_of
, assembly.QUANTITY as qty_on_assembly
, 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
order by base_part.PART_NO
;