|
Hello, I have a table (id, name, mom_id, dad_id) I want to print the name at the place of id by the self join. Can anyone help me out? sample table is : id, name, mom_id, dad_id 8,Mike,1,5 1,Izabela,6,7 5,John,8,9 I want output: name, mom, dad Mike,Izabela,John Method: Sql Join |
|
Hello, maybe that can help: select nam."id", nam."name", mom."name" as mom, dad."name" as dad from _NAMES nam left outer join _NAMES mom on nam."MOM_ID" = mom."ID" left outer join _NAMES dad on nam."DAD_ID" = dad."ID" To create my test scenario I have executed this: create table _NAMES( "ID" integer, "NAME" varchar(32), "MOM_ID" integer, "DAD_ID" integer, CONSTRAINT "PK_ID" PRIMARY KEY ( "ID" ASC ) ) IN "system"; insert into _NAMES("ID","NAME","MOM_ID","DAD_ID") values(8,'Mike',1,5); insert into _NAMES("ID","NAME","MOM_ID","DAD_ID") values(1,'Izabella',6,7); insert into _NAMES("ID","NAME","MOM_ID","DAD_ID") values(5,'John',8,9); Hope that helps Frank If you only would like to list a person with both a known mom and dad, use two INNER JOINs instead of the LEFT OUTER JOINs...
(16 Nov '13, 18:33)
Volker Barth
Replies hidden
1
Beware the PC Police: "mom" and "dad" assumes male and female, INNER JOIN assumes exactly two parents, and the foreign key relationships assumes no more than two parents :)
(17 Nov '13, 17:33)
Breck Carter
|
