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
Solved

Get all parents from sql query

Posted on 2010-08-26
6
415 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
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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
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 500 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

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

809 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