[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 424
  • Last Modified:

Get all parents from sql query

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
peternordberg
Asked:
peternordberg
1 Solution
 
kingjelyCommented:
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
 
Om PrakashCommented:
Please check the following thread:
http://www.vbforums.com/showthread.php?t=366078
0
 
peternordbergAuthor Commented:
I want the selected hiearchy based on what categoryID is chosen, like a sitepath (breadcrunch)

Peter
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
Jini Jose.Net Team LeadCommented:
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
 
rushShahCommented:
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
 
CluskittCommented:
Nice elegant solution, RushShah! Congrats :)
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now