Tech or Treat! Write an article about your scariest tech disaster to win gadgets!Learn more

x
?
Solved

Query information from single table with parent-child relationship

Posted on 2012-03-21
3
Medium Priority
?
159 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 2000 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 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.
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Viewers will learn the different options available in the Backstage view in Excel 2013.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

647 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