Parent-Child Relationship Join

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)

Parent-Child Relationship Join

Author

Topic

Brittney10
Posting Yak Master

154 Posts

Posted - 2013-10-31 : 12:42:08

I have a table that contains both Parent and Child records and i'm trying to return the Parent data, if a child record has a parent.
For example (ABC is the child and DEF is the Parent):

ID______CNumber_____PNumber___AR Amount
1_______ABC_________DEF_______5,0000
2_______DEF_________NULL______10,000

What I hope to return is this (note Child and Parent records contain the same AR Amount now):

ID______CNumber_____PNumber___AR Amount
1_______ABC_________DEF_______10,000
2_______DEF_________NULL______10,000

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-31 : 13:45:02

Do you have multiple levels (as in children, grand children, great grand children etc.)? If so, what do you want to display?

If it is just a simple parent child relationship as in your example, this should be sufficient:

SELECT
	c.Id,
	c.CNumber,
	c.PNumber,
	COALESCE(p.ARAmount,c.ARAmount) AS ARAmount
FROM
	YourTable c
	LEFT JOIN YourTable p ON c.PNumber = p.CNumber

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources