Query information from single table with parent-child relationship
Posted on 2012-03-21
I have a fairly simple database that is giving me issues. The main table (tblCategory) is structured as such:
ID Category ParentCategoryID
1 Parent NULL
2 Child 1
Ideally when I execute the query I would get the following results:
ID Category ParentCategoryID ParentCategory
1 Parent NULL NULL
2 Child 1 Parent
This is the SQL query I wrote to try to pull the information:
SELECT ID, Category, ParentCategoryID, (SELECT Category FROM tblCategory WHERE tblCategory.ParentCategoryID = tblCategory.ID) AS ParentCategory
I know I could use two tables to do this; however the data structure for the both the parent and child recordsets are identicle. I also like the idea of a recursive relationship data structure that could allow for infinite levels without having to create a table each time a new level of data is added.
I appreciate your help and feedback.