?
Solved

Get all parents from sql query

Posted on 2010-08-26
6
Medium Priority
?
421 Views
Last Modified: 2012-05-10
Hi,

I have a table looking like this:
categoryId     category     parentID
0                    Home          Null
1                    furniture     0
2                    Cars           0
3                    Chair          1
4                    Volvo         2

Now I want to be able to select the hierarchy based on the categoryID, so that if the categoryID for example is 4 then the result of the query would be:
categoryID        category     parentID
0                       Home          Null
2                       Cars            0
4                       Volvo          2

How can I achieve this?

Thanks for help!

Peter
0
Comment
Question by:peternordberg
[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
6 Comments
 
LVL 8

Expert Comment

by:kingjely
ID: 33530869
another example might help but,

select CategoryID, category, parentID
From YourTable
order By categoryID asc

Yea nah, i don't see what your trying to do at all..
0
 
LVL 22

Expert Comment

by:Om Prakash
ID: 33530892
Please check the following thread:
http://www.vbforums.com/showthread.php?t=366078
0
 

Author Comment

by:peternordberg
ID: 33530893
I want the selected hiearchy based on what categoryID is chosen, like a sitepath (breadcrunch)

Peter
0
What is a Denial of Service (DoS)?

A DoS is a malicious attempt to prevent the normal operation of a computer system. You may frequently see the terms 'DDoS' (Distributed Denial of Service) and 'DoS' used interchangeably, but there are some subtle differences.

 
LVL 10

Expert Comment

by:Jini Jose
ID: 33530926
you can use hierarchycte to get all parents of a child and all childs and sub childs of a parent.

here is the sample and usage.

http://www.sqlservercentral.com/Forums/Topic796015-338-1.aspx
0
 
LVL 8

Accepted Solution

by:
rushShah earned 2000 total points
ID: 33530960
hi if you are using sql server 2005 or above than try this..


;WITH CTE AS
(
	SELECT	categoryId, category, parentID
	FROM	yourtable
	WHERE	categoryId=4
	UNION ALL
	SELECT	t1.categoryId, t1.category, t1.parentID
	FROM	yourtable t1
	INNER JOIN	CTE c ON c.ParentID=t1.CategoryID
)

SELECT	* FROM CTE ORDER BY CategoryID

Open in new window

0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 33531230
Nice elegant solution, RushShah! Congrats :)
0

Featured Post

Interactive Way of Training for the AWS CSA Exam

An interactive way of learning that will help you visualize core concepts so that you can be more effective when taking your AWS certification exam.  Built for students by a student to help them understand the concepts that they are being taught.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

777 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