Hello,
I am stuck on trying to write a SQL query (PostgreSQL 10.6) that I
cannot get my head around, and I cannot find anything after googling
for a while.
There are 2 tables:
CREATE TABLE car_parts (
id integer PRIMARY KEY,
name text
);
CREATE TABLE bill_of_materials (
parent_id integer REFERENCES car_parts (id),
child_id integer REFERENCES car_parts (id)
);
SELECT * FROM car_parts;
id | name
---------------------
01 | "Assembled 4WD"
02 | "Assembled 2WD"
03 | "V8 Engine"
04 | "V6 Engine"
05 | "Tow Bar"
SELECT * FROM bill_of_materials;
parent_id | child_id
--------------------
01 | 03
01 | 05
02 | 04
02 | 05
Question is,
How do I write an SQL query so that the "name" text in car_parts are
added to the bill_of_materials table, so that it looks like this:
parent_id | parent_name | child_id | child_name
------------------------------------------------------
01 | "Assembled 4WD" | 03 | "V8 Engine"
01 | "Assembled 4WD" | 05 | "Tow Bar"
02 | "Assembled 2WD" | 04 | "V6 Engine"
02 | "Assembled 2WD" | 05 | "Tow Bar"
Thank you.
Regards,
Ian