Parent Child Record Relationship in SQL?

Question

I need to figure out the best way, in SQL, to query on a Parent / Child relationship. Some of the parent fields will be the data for the child. Here is an example:

ID     Field1       Field2         ParentId
--------------------------------------------
1      stuff        moreStuff      0
2      childStuff   (from parent)  1

So, Field2 for a child would be the value of whatever the parent's is. I need to figure out how to write my SQL so that when the records get pulled back Field@ for the child would be "moreStuff". I am using SQL server 2008. Thank you.

Answer 1

A self join should do the trick for you in this case.

SELECT child.ID, 
       child.Field1, 
       parent.Field2, 
       child.ParentID
FROM   MyTable child JOIN MyTable parent ON child.ParentID = parent.ID

Answer 2

Use a self join on the table:

SELECT parent.Field1, parent.Field2, child.ID
FROM myTable child
  INNER JOIN myTable parent
  ON child.ParentId = parent.ID

Answer 3

Assuming Field2 cannot be NULL, you could use a LEFT JOIN with COALESCE:

SELECT T1.ID, T1.Field1, COALESCE(T2.Field2, T1.Field2) AS Field2, T1.ParentID
FROM Table1 T1
LEFT JOIN Table1 T2
ON T1.ParentID = T2.ID

If that Field2 can be NULL, replace the coalesce expression with the following:

CASE WHEN T2.Id IS NULL THEN T1.Field2 ELSE T2.Field2 END AS Field2

Answer 4

It is common for these kind of hierarchical queries to have a root node, a prime mover, a parent who is not themselves a child. That is, records where ParentId is null (or in your case 0, which I presume is not a real ID). If such be the case here you'll need to use an OUTER join rather than an INNER one...

SELECT parent.Field1, parent.Field2, child.ID 
FROM myTable child 
    LEFT OUTER JOIN myTable parent 
    ON child.ParentId = parent.ID 
/

Answer 5

Here is a pretty good article on Storing Hierarchical Data in a Database geared towared PHP/MySQL users: http://articles.sitepoint.com/article/hierarchical-data-database