Help on SQL query

Поиск
Список
Период
Сортировка
От Ian Tan
Тема Help on SQL query
Дата
Msg-id CAAj7RQ-vF3kPaC7Y5csgjL75Bo=FotP6W=s2LsRhirVE78b4HQ@mail.gmail.com
обсуждение исходный текст
Ответы Re: Help on SQL query  (Andrew Gierth <andrew@tao11.riddles.org.uk>)
Список 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


В списке pgsql-sql по дате отправления:

Предыдущее
От: Sven Berkvens-Matthijsse
Дата:
Сообщение: Re: Idea: INSERT INTO ... NATURAL SELECT ...
Следующее
От: Andrew Gierth
Дата:
Сообщение: Re: Help on SQL query