Help on SQL query

From: Ian Tan <iantan1268(at)gmail(dot)com>
To: pgsql-sql(at)lists(dot)postgresql(dot)org
Subject: Help on SQL query
Date: 2019-02-15 17:22:31
Message-ID: CAAj7RQ-vF3kPaC7Y5csgjL75Bo=FotP6W=s2LsRhirVE78b4HQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Andrew Gierth 2019-02-15 17:28:34 Re: Help on SQL query
Previous Message Michael Lewis 2019-02-14 22:58:25 Re: Postgresql RDS DB Latency Chossing Hash join Plan