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

asked 16 Nov '13, 07:20

shashank29's gravatar image

shashank29
0112
accept rate: 0%

edited 16 Nov '13, 07:23


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

permanent link

answered 16 Nov '13, 11:03

Frank_V's gravatar image

Frank_V
31236
accept rate: 0%

edited 16 Nov '13, 12:58

Mark%20Culp's gravatar image

Mark Culp
23.2k9132273

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
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×90
×21

question asked: 16 Nov '13, 07:20

question was seen: 948 times

last updated: 17 Nov '13, 17:33