Self Join T-SQL Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Self Join T-SQL Query

Author

Topic

bhav27
Starting Member

8 Posts

Posted - 2007-10-06 : 07:08:21

Hello Everyone,
I've following table
PersonID PersonName FatherPersonID MotherPersonID
1 Bob 4 5
2 Carol 4 5
3 Jennifer 4 5
4 Frank 9 10
5 Michelle 11 12
6 Keith 3 13
7 Natasha 3 13
8 Alice 2 14
9 Patrick
10 Julie
11 Jim
12 Anne
13 David 15 16
14 Raymond
15 Eric
16 Natalie

I want to write a SELECT statement to extract the names of all of Bob’s grandparents

This is what I have done

Select gf.PersonName As GrandFather, gm.PersonName As GrandMother
From Person gf, Person gm, Person f, Person m, Person b Where

(gf.PersonId = f.FatherPersonId OR
gf.PersonId = m.FatherPersonId) AND

(gm.PersonId = f.MotherPersonId OR
gm.PersonId = m.MotherPersonId) AND

f.PersonId = b.FatherPersonId AND
m.PersonId = b.MotherPersonId AND

b.PersonId = 1 --Bob

but the query result is not perfect.

Please can someone advice me the correct query using both implicit and explicit join.

many thanks

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2007-10-06 : 08:07:15

Select gf.PersonName As GrandFather,mf.PersonName As GrandMother
FROM myTable as m
INNER JOIN (SELECT FatherPersonID,motherPersonID FROM myTable) as f ON m.FatherPersonID = f.FatherPersonID
AND m.MotherPersonID = f.MotherPersonID
INNER JOIN (SELECT FatherPersonID,fatherPerson FROM myTable) as gf ON f.fatherPersonID = gf.FatherPersonID
AND f.motherPeronID = f2.motherPersonID
INNER JOIN (SELECT MotherPersonID,person FROM myTable) as mf ON f.motherPersonID = mf.MotherPersonID

WHERE m.PersonID =1

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

Kristen
Test

22859 Posts

Posted - 2007-10-06 : 08:08:36

[code]
DECLARE @Person TABLE
(
PersonID int NOT NULL,
PersonName varchar(20),
FatherPersonID int,
MotherPersonID int
)

-- PersonID PersonName FatherPersonID MotherPersonID
INSERT INTO @Person
SELECT 1, 'Bob', 4, 5 UNION ALL
SELECT 2, 'Carol', 4, 5 UNION ALL
SELECT 3, 'Jennifer', 4, 5 UNION ALL
SELECT 4, 'Frank', 9, 10 UNION ALL
SELECT 5, 'Michelle', 11, 12 UNION ALL
SELECT 6, 'Keith', 3, 13 UNION ALL
SELECT 7, 'Natasha', 3, 13 UNION ALL
SELECT 8, 'Alice', 2, 14 UNION ALL
SELECT 9, 'Patrick', NULL, NULL UNION ALL
SELECT 10, 'Julie', NULL, NULL UNION ALL
SELECT 11, 'Jim', NULL, NULL UNION ALL
SELECT 12, 'Anne', NULL, NULL UNION ALL
SELECT 13, 'David', 15, 16 UNION ALL
SELECT 14, 'Raymond', NULL, NULL UNION ALL
SELECT 15, 'Eric', NULL, NULL UNION ALL
SELECT 16, 'Natalie', NULL, NULL

SELECT [Person] = P.PersonName,
[Mother] = M.PersonName,
[Father] = F.PersonName,
[Mother's Mother] = MM.PersonName,
[Mother's Father] = MF.PersonName,
[Father's Mother] = FM.PersonName,
[Father's Father] = FF.PersonName
FROM @Person AS P
LEFT OUTER JOIN @Person AS M -- Mother
ON M.PersonID = P.MotherPersonID
LEFT OUTER JOIN @Person AS F -- Father
ON F.PersonID = P.FatherPersonID
LEFT OUTER JOIN @Person AS MM -- Mother's Mother
ON MM.PersonID = M.MotherPersonID
LEFT OUTER JOIN @Person AS MF -- Mother's Father
ON MF.PersonID = M.FatherPersonID
LEFT OUTER JOIN @Person AS FM -- Father's Mother
ON FM.PersonID = F.MotherPersonID
LEFT OUTER JOIN @Person AS FF -- Father's Father
ON FF.PersonID = F.FatherPersonID
WHERE P.PersonId = 1 --Bob
[/code]

bhav27
Starting Member

8 Posts

Posted - 2007-10-06 : 08:15:31

quote:
Originally posted by jackv

Select gf.PersonName As GrandFather,mf.PersonName As GrandMother
FROM myTable as m
INNER JOIN (SELECT FatherPersonID,motherPersonID FROM myTable) as f ON m.FatherPersonID = f.FatherPersonID
AND m.MotherPersonID = f.MotherPersonID
INNER JOIN (SELECT FatherPersonID,fatherPerson FROM myTable) as gf ON f.fatherPersonID = gf.FatherPersonID
AND f.motherPeronID = f2.motherPersonID
INNER JOIN (SELECT MotherPersonID,person FROM myTable) as mf ON f.motherPersonID = mf.MotherPersonID

WHERE m.PersonID =1

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com/SQL

Jack this is incorrect query here is what i get

Msg 207, Level 16, State 1, Line 6
Invalid column name 'fatherPerson'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'motherPeronID'.
Msg 4104, Level 16, State 1, Line 7
The multi-part identifier "f2.motherPersonID" could not be bound.
Msg 207, Level 16, State 1, Line 8
Invalid column name 'person'.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'PersonName'.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'PersonName'.

bhav27
Starting Member

8 Posts

Posted - 2007-10-06 : 08:18:41

quote:

Originally posted by Kristen


DECLARE @Person TABLE
(
	PersonID int NOT NULL,
	PersonName varchar(20),
	FatherPersonID int,
	MotherPersonID int
)

-- PersonID PersonName FatherPersonID MotherPersonID
INSERT INTO @Person
SELECT 1, 'Bob', 4, 5 UNION ALL
SELECT 2, 'Carol', 4, 5 UNION ALL
SELECT 3, 'Jennifer', 4, 5 UNION ALL
SELECT 4, 'Frank', 9, 10 UNION ALL
SELECT 5, 'Michelle', 11, 12 UNION ALL
SELECT 6, 'Keith', 3, 13 UNION ALL
SELECT 7, 'Natasha', 3, 13 UNION ALL
SELECT 8, 'Alice', 2, 14 UNION ALL
SELECT 9, 'Patrick', NULL, NULL UNION ALL
SELECT 10, 'Julie', NULL, NULL UNION ALL
SELECT 11, 'Jim', NULL, NULL UNION ALL
SELECT 12, 'Anne', NULL, NULL UNION ALL
SELECT 13, 'David', 15, 16 UNION ALL
SELECT 14, 'Raymond', NULL, NULL UNION ALL
SELECT 15, 'Eric', NULL, NULL UNION ALL
SELECT 16, 'Natalie', NULL, NULL

SELECT	[Person] = P.PersonName,
	[Mother] = M.PersonName,
	[Father] = F.PersonName,
	[Mother's Mother] = MM.PersonName,
	[Mother's Father] = MF.PersonName,
	[Father's Mother] = FM.PersonName,
	[Father's Father] = FF.PersonName
FROM	@Person AS P
	LEFT OUTER JOIN @Person AS M	-- Mother
		ON M.PersonID = P.MotherPersonID
	LEFT OUTER JOIN @Person AS F	-- Father
		ON F.PersonID = P.FatherPersonID
	LEFT OUTER JOIN @Person AS MM	-- Mother's Mother
		ON MM.PersonID = M.MotherPersonID
	LEFT OUTER JOIN @Person AS MF	-- Mother's Father
		ON MF.PersonID = M.FatherPersonID
	LEFT OUTER JOIN @Person AS FM	-- Father's Mother
		ON FM.PersonID = F.MotherPersonID
	LEFT OUTER JOIN @Person AS FF	-- Father's Father
		ON FF.PersonID = F.FatherPersonID
WHERE	P.PersonId = 1 --Bob

This is correct... but can you advice what I have done wrong in the implict join query below

Select gf.PersonName As GrandFather, gm.PersonName As GrandMother
From Person gf, Person gm, Person f, Person m, Person b Where

(gf.PersonId = f.FatherPersonId OR
gf.PersonId = m.FatherPersonId) AND

(gm.PersonId = f.MotherPersonId OR
gm.PersonId = m.MotherPersonId) AND

f.PersonId = b.FatherPersonId AND
m.PersonId = b.MotherPersonId AND

b.PersonId = 1 --Bob

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2007-10-06 : 08:20:24

OK, try this one , I had a few mispellings.

Select gf.PersonName As GrandFather,mf.PersonName As GrandMother
FROM myTable as m
INNER JOIN (SELECT FatherPersonID,motherPersonID FROM myTable) as f ON m.FatherPersonID = f.FatherPersonID
AND m.MotherPersonID = f.MotherPersonID
INNER JOIN (SELECT FatherPersonID,PersonName FROM myTable) as gf ON f.fatherPersonID = gf.FatherPersonID
AND f.motherPersonID = f.motherPersonID
INNER JOIN (SELECT MotherPersonID,PersonName FROM myTable) as mf ON f.motherPersonID = mf.MotherPersonID

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com/SQL

bhav27
Starting Member

8 Posts

Posted - 2007-10-06 : 08:26:56

quote:
Originally posted by jackv

OK, try this one , I had a few mispellings.

Select gf.PersonName As GrandFather,mf.PersonName As GrandMother
FROM myTable as m
INNER JOIN (SELECT FatherPersonID,motherPersonID FROM myTable) as f ON m.FatherPersonID = f.FatherPersonID
AND m.MotherPersonID = f.MotherPersonID
INNER JOIN (SELECT FatherPersonID,PersonName FROM myTable) as gf ON f.fatherPersonID = gf.FatherPersonID
AND f.motherPersonID = f.motherPersonID
INNER JOIN (SELECT MotherPersonID,PersonName FROM myTable) as mf ON f.motherPersonID = mf.MotherPersonID

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com/SQL

I've solved without using explict joins

Select gf1.PersonName As [Father's Father], gm1.PersonName As [Father's Mother],
gf2.PersonName As [Mother's Father], gm2.PersonName As [Mother's Mother]

From Person gf1, Person gm1, Person gf2, Person gm2,
Person f, Person m, Person b Where

gf1.PersonId = f.FatherPersonId AND
gm1.PersonId = f.MotherPersonId AND

gf2.PersonId = m.FatherPersonId AND
gm2.PersonId = m.MotherPersonId AND

f.PersonId = b.FatherPersonId AND
m.PersonId = b.MotherPersonId AND

b.PersonId = 1 --Bob

bhav27
Starting Member

8 Posts

Posted - 2007-10-06 : 08:28:56

quote:
Originally posted by jackv

OK, try this one , I had a few mispellings.

Select gf.PersonName As GrandFather,mf.PersonName As GrandMother
FROM myTable as m
INNER JOIN (SELECT FatherPersonID,motherPersonID FROM myTable) as f ON m.FatherPersonID = f.FatherPersonID
AND m.MotherPersonID = f.MotherPersonID
INNER JOIN (SELECT FatherPersonID,PersonName FROM myTable) as gf ON f.fatherPersonID = gf.FatherPersonID
AND f.motherPersonID = f.motherPersonID
INNER JOIN (SELECT MotherPersonID,PersonName FROM myTable) as mf ON f.motherPersonID = mf.MotherPersonID

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com/SQL

this is not correct yet..... it shows 101 rows in the result set

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2007-10-06 : 08:31:39

add "WHERE m.PersonId = 1"

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com/SQL

bhav27
Starting Member

8 Posts

Posted - 2007-10-06 : 08:36:13

quote:
Originally posted by jackv

add "WHERE m.PersonId = 1"

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com/SQL

dude try this

SELECT [Person] = P.PersonName,
[Mother] = M.PersonName,
[Father] = F.PersonName,
[Mother's Mother] = MM.PersonName,
[Mother's Father] = MF.PersonName,
[Father's Mother] = FM.PersonName,
[Father's Father] = FF.PersonName
FROM @Person AS P
INNER JOIN @Person AS M -- Mother
ON M.PersonID = P.MotherPersonID
INNER JOIN @Person AS F -- Father
ON F.PersonID = P.FatherPersonID
INNER JOIN @Person AS MM -- Mother's Mother
ON MM.PersonID = M.MotherPersonID
INNER JOIN @Person AS MF -- Mother's Father
ON MF.PersonID = M.FatherPersonID
INNER JOIN @Person AS FM -- Father's Mother
ON FM.PersonID = F.MotherPersonID
INNER JOIN @Person AS FF -- Father's Father
ON FF.PersonID = F.FatherPersonID
WHERE P.PersonId = 1 --Bob

just replaced LEFT OUTER JOIN to INNER JOIN from kristen's reply

masteripper
Starting Member

25 Posts

Posted - 2011-06-14 : 03:22:42

Well it should go something like this (is not perfect)
WITH GrandFather(ChildID, GrandChildName, GrandFatherID,iteration) AS
(
SELECT p.PersonID,
p.PersonName,
p.PersonID,
0
FROM person p
WHERE p.FatherPersonID IS NULL
UNION ALL SELECT p1.PersonID,
p1.PersonName,
f.GrandFatherID,
f.iteration+1
FROM person p1
INNER JOIN Grandfather f
ON p1.FatherPersonId = f.ChildID
)

SELECT Grandfather.*,p1.personname as GrandFatherName
FROM Grandfather INNER JOIN Person p1
ON Grandfather.GrandfatherID = p1.personID

WHERE Grandfather.iteration =2

Well this work when the table person is like this
INSERT INTO [person] ([PersonID],[PersonName],[FatherPersonID],[MotherPersonID])VALUES(1,'Bob',4,5)
INSERT INTO [person] ([PersonID],[PersonName],[FatherPersonID],[MotherPersonID])VALUES(2,'Carol',4,5)
INSERT INTO [person] ([PersonID],[PersonName],[FatherPersonID],[MotherPersonID])VALUES(3,'Jennifer',4,5)
INSERT INTO [person] ([PersonID],[PersonName],[FatherPersonID],[MotherPersonID])VALUES(4,'Frank',9,10)
INSERT INTO [person] ([PersonID],[PersonName],[FatherPersonID],[MotherPersonID])VALUES(5,'Michelle',11,12)
INSERT INTO [person] ([PersonID],[PersonName],[FatherPersonID],[MotherPersonID])VALUES(6,'Keith',11,12)
INSERT INTO [person] ([PersonID],[PersonName],[FatherPersonID],[MotherPersonID])VALUES(7,'Natasha',11,12)
INSERT INTO [person] ([PersonID],[PersonName],[FatherPersonID],[MotherPersonID])VALUES(8,'Alice',15,16)
INSERT INTO [person] ([PersonID],[PersonName],[FatherPersonID],[MotherPersonID])VALUES(9,'Patrick',NULL,NULL)
INSERT INTO [person] ([PersonID],[PersonName],[FatherPersonID],[MotherPersonID])VALUES(10,'Julie',NULL,NULL)
INSERT INTO [person] ([PersonID],[PersonName],[FatherPersonID],[MotherPersonID])VALUES(11,'Jim',NULL,NULL)
INSERT INTO [person] ([PersonID],[PersonName],[FatherPersonID],[MotherPersonID])VALUES(12,'Anne',NULL,NULL)
INSERT INTO [person] ([PersonID],[PersonName],[FatherPersonID],[MotherPersonID])VALUES(13,'David',15,16)
INSERT INTO [person] ([PersonID],[PersonName],[FatherPersonID],[MotherPersonID])VALUES(14,'Raymond',NULL,NULL)
INSERT INTO [person] ([PersonID],[PersonName],[FatherPersonID],[MotherPersonID])VALUES(15,'Eric',NULL,NULL)
INSERT INTO [person] ([PersonID],[PersonName],[FatherPersonID],[MotherPersonID])VALUES(16,'Natalie',NULL,NULL)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-06-14 : 03:31:32

FYI. You just dig out a 4 year old thread.

KH
[spoiler]Time is always against us[/spoiler]

masteripper
Starting Member

25 Posts

Posted - 2011-06-14 : 06:08:56

Well my bad....i was searching for something......and mistakes happen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources