Parent child account in SQL?

Question

I need to figure out how to best query the parent / child relationship in SQL. Some of the parent fields will be data for the child. Here is an example:

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

So, Field2 for the child will be the value of any parent. I need to figure out how to write my SQL so that when stripping Field @ entries for child would be "moreStuff". I am using SQL Server 2008. Thank you.

Answer 1

Assuming Field2 cannot be NULL, you can use 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 this field2 can be NULL, replace the coalesce expression as follows:

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

Answer 2

.

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

Answer 3

self join :

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

Answer 4

node, , , . , ParentId null ( 0, , , ). , OUTER, INNER...

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

Answer 5

, PHP/MySQL: http://articles.sitepoint.com/article/hierarchical-data-database