Query information from single table with parent-child relationship

Posted on 2012-03-21
Medium Priority
Last Modified: 2014-06-03
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
FROM tblCategory;

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.
Question by:Bushmouse
LVL 20

Accepted Solution

BuggyCoder earned 2000 total points
ID: 37747420
Read this URL to know more about how to access Data from Table having recursive information:-


The term to query is Common Table Expression and is completely related to SQL Server 2005
LVL 23

Expert Comment

by:Steve Wales
ID: 40107947
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In this article, we will see two different methods to recover deleted data. The first option will be using the transaction log to identify the operation and restore it in a specified section of the transaction log. The second option is simpler and c…
An introductory discussion about Oracle Analytic Functions which are used to calculate or compute Aggregate values, based on a group of rows.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question