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

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 2

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