CTE Query Parent/Child Struggles

Question

CTE Query Parent/Child Struggles

malik.robinson

Grasshopper

Points: 23
More actions
February 13, 2011 at 5:52 pm

#234944

Hi,
I'm building a system where I need to be able to show product categories above and below a given category_id.
Can someone kindly provide an example of a CTE query that shows a list of children and parents of a given category, given the schema below.
category_id (int),
category_name (varchar 100),
parent_id (int)
If the category_id is 47 for example, I want to show all parent categories and all child categories. A given category_id may have more than one parent and more than one child. If the query can show the level in the hierarchy that'd be even better, but I won't ask for too much here.
Any help appreciated.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95450 More actions · Answer 1

You know, the people that help out here are all un-paid volunteers, so please HELP US HELP YOU. Providing the DDL scripts (CREATE TABLE, CREATE INDEX, etc.) for the tables affected, and INSERT statements to put some test data into those tables that shows your problem will go a long way in getting people to look at your issue and help you out. Please include code for what you have already tried. Don't forget to include what your expected results should be, based on the sample data provided. As a bonus to you, you will get tested code back. For more details on how to get all of this into your post, please look at the first link in my signature.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

malik.robinson Grasshopper Points: 23 More actions · Answer 2

Thanks for the warm welcome on my first post in this forum. I tried to be brief purely to avoid a long monolithic post.

I will keep trying to solve the issue myself.

diamondgm SSCrazy Points: 2824 More actions · Answer 3

I googled 'sql server cte hierarchy tutorial' and came up with some results that would certainly help you. Failing that, typing 'WITH' into SSMS, highlighting it and pushing F1 (BOL), will return you a fairly comprehensive explanation of how CTEs work; examples included.

Jeff Moden SSC Guru Points: 1004190 More actions · Answer 4

malik.robinson (2/13/2011)
Thanks for the warm welcome on my first post in this forum. I tried to be brief purely to avoid a long monolithic post.
I will keep trying to solve the issue myself.

Heh... I agree that Wayne's post is a bit terse to use on a newbie. He does have your best interest at heart though because folks on this forum try to give tested coded answers.

Anyway, I'll be back in a few minutes with a coded example including some test data. In the meantime, lookup "Recursive CTEs" in Books Online so you know what I'm doing in the example. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004190 More actions · Answer 5

Ack! I just reread your requirements. The part about multiple-parents for a given category ID isn't something easily resolved. Are you sure about the "multiple-parents" thing?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

malik.robinson Grasshopper Points: 23 More actions · Answer 6

Thanks for the replies. I did end up getting it to work...it uses two CTEs. Here is the code I ended up with. If you notice anything that could be done different, please let me know, but it does work. I've hard-coded the category_id

With cteUp As

(

-- Anchor

Select category_id, category_name, parent_id

From Category_Tree

Where category_id = 23

Union All

Select t.category_id, t.category_name, t.parent_id

From Category_Tree t

Inner Join cteUp c On c.parent_id = t.category_id),

cteDown As

(

-- Anchor

Select category_id, category_name, parent_id

From Category_Tree

Where parent_id = 23

Union All

Select t.category_id, t.category_name, t.parent_id

From Category_Tree t

Inner Join cteDown c On c.category_id = t.parent_id)

Select category_id, category_name, parent_id

From cteUp

Union All

Select category_id, category_name, parent_id

From cteDown;

Regards

Jeff Moden SSC Guru Points: 1004190 More actions · Answer 7

malik.robinson (2/14/2011)
Thanks for the replies. I did end up getting it to work...it uses two CTEs. Here is the code I ended up with. If you notice anything that could be done different, please let me know, but it does work. I've hard-coded the category_id

That certainly works. It's too bad that you have the possibility of multiple parents... I was going to show you a method to convert the entire hierarchy to a high speed "Nested Sets" method but it won't work in a multi-parent situation.

And thanks for the feedback. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)