Solved

Query information from single table with parent-child relationship

Posted on 2012-03-21
3
154 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
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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Outlook 2012 VBA: Object missing 14 35
SQL NULL vs Blank 26 36
T-SQL Query to include null values 3 35
Dcount using a date in a table compared to today's date 3 32
Many companies are making the switch from Microsoft to Google Apps (https://www.google.com/work/apps/business/). Use this article to learn more about what Google Apps has to offer and to help if you’re planning on migrating to Google Apps. It is …
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
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 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 …

825 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