Thread: Help on SQL query
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
>>>>> "Ian" == Ian Tan <iantan1268@gmail.com> writes:
Ian> How do I write an SQL query so that the "name" text in car_parts
Ian> are added to the bill_of_materials table, so that it looks like
Ian> this:
The only trick with this is that you need to join the car_parts table
twice (once for parent and once for child), and to do that you need to
give it different alias names:
select bom.parent_id,
ppart.name as parent_name,
bom.child_id,
cpart.name as child_name
from bill_of_materials bom
join car_parts ppart on (ppart.id=bom.parent_id)
join car_parts cpart on (cpart.id=bom.child_id);
--
Andrew (irc:RhodiumToad)
> On Feb 15, 2019, at 10:28 AM, Andrew Gierth <andrew@tao11.riddles.org.uk> wrote: > >>>>>> "Ian" == Ian Tan <iantan1268@gmail.com> writes: > > Ian> How do I write an SQL query so that the "name" text in car_parts > Ian> are added to the bill_of_materials table, so that it looks like > Ian> this: > > The only trick with this is that you need to join the car_parts table > twice (once for parent and once for child), and to do that you need to > give it different alias names: > > select bom.parent_id, > ppart.name as parent_name, > bom.child_id, > cpart.name as child_name > from bill_of_materials bom > join car_parts ppart on (ppart.id=bom.parent_id) > join car_parts cpart on (cpart.id=bom.child_id); > > -- > Andrew (irc:RhodiumToad) > Andrew, will you do my homework too?
Hello Andrew, Thank you, I appreciate your response and your help. Hello Rob, I learn in my own time and had no one to ask. If pgsql-sql is not the correct forum for these kinds of question, kindly let me know. Thank you. Regards, Ian On Fri, 15 Feb 2019 at 17:38, Rob Sargent <robjsargent@gmail.com> wrote: > > > > > On Feb 15, 2019, at 10:28 AM, Andrew Gierth <andrew@tao11.riddles.org.uk> wrote: > > > >>>>>> "Ian" == Ian Tan <iantan1268@gmail.com> writes: > > > > Ian> How do I write an SQL query so that the "name" text in car_parts > > Ian> are added to the bill_of_materials table, so that it looks like > > Ian> this: > > > > The only trick with this is that you need to join the car_parts table > > twice (once for parent and once for child), and to do that you need to > > give it different alias names: > > > > select bom.parent_id, > > ppart.name as parent_name, > > bom.child_id, > > cpart.name as child_name > > from bill_of_materials bom > > join car_parts ppart on (ppart.id=bom.parent_id) > > join car_parts cpart on (cpart.id=bom.child_id); > > > > -- > > Andrew (irc:RhodiumToad) > > > > Andrew, will you do my homework too?
On 2/15/19 11:16 AM, Ian Tan wrote: > Hello Andrew, > Thank you, I appreciate your response and your help. > > Hello Rob, > I learn in my own time and had no one to ask. If pgsql-sql is not the > correct forum for these kinds of question, kindly let me know. > > Thank you. > > Regards, > Ian > > On Fri, 15 Feb 2019 at 17:38, Rob Sargent <robjsargent@gmail.com> wrote: >> >> >>> On Feb 15, 2019, at 10:28 AM, Andrew Gierth <andrew@tao11.riddles.org.uk> wrote: >>> >>>>>>>> "Ian" == Ian Tan <iantan1268@gmail.com> writes: >>> Ian> How do I write an SQL query so that the "name" text in car_parts >>> Ian> are added to the bill_of_materials table, so that it looks like >>> Ian> this: >>> >>> The only trick with this is that you need to join the car_parts table >>> twice (once for parent and once for child), and to do that you need to >>> give it different alias names: >>> >>> select bom.parent_id, >>> ppart.name as parent_name, >>> bom.child_id, >>> cpart.name as child_name >>> from bill_of_materials bom >>> join car_parts ppart on (ppart.id=bom.parent_id) >>> join car_parts cpart on (cpart.id=bom.child_id); >>> >>> -- >>> Andrew (irc:RhodiumToad) >>> >> Andrew, will you do my homework too? My apologies. It looked suspiciously like a homework question to me.