Sql join query

[Former Member]

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

[Former Member]

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

[VolkerBarth]

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...

[Breck_Carter]

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 🙂