Help on SQL query

Ian Tan <[email protected]> Fri, 15 Feb 2019 17:22:31 +0000
Newsgroups gmane.comp.db.postgresql.sql
Message-ID <CAAj7RQ-vF3kPaC7Y5csgjL75Bo=FotP6W=s2LsRhirVE78b4HQ@mail.gmail.com>
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