Solved

Query information from single table with parent-child relationship

Posted on 2012-03-21
3
155 Views
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.
0
Comment
Question by:Bushmouse
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 20

Accepted Solution

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

http://msdn.microsoft.com/en-us/library/ms186243.aspx

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

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.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This collection of functions covers all the normal rounding methods of just about any numeric value.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

733 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