Self Join

Question

Post reply

Self Join

deepika-377957

SSC Rookie

Points: 26
More actions
February 19, 2007 at 5:17 am

#172135

Hi,

I have a table that contains categoryid, categoryname and basecategoryid.

For every category, the parent categoryid is stored in the basecategoryid field.

Each category can have many levels of sub categories.

I want a query which will retrieve all the sub and sub sub categories( till the last level), given a categoryid.

Appreciate help in this regard.

regards,

Deepika.

Viewing 6 posts - 1 through 5 (of 5 total)

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

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

What have you tried?

--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)
Intro to Tally Tables and Functions

deepika-377957 SSC Rookie Points: 26 More actions · Answer 2

I am able to drill down only to one level.

I used the following query:

SELECT c1.categoryid as ParentCategoryID, c2.categoryid as SubCategoryID, c1.categoryname AS ParentCategory, c2.categoryname AS SubCatgeory

FROM category AS c1 INNER JOIN category AS c2 ON c1.categoryid=c2.basecategoryid

ORDER BY c1.categoryid;

Johan Bijnens SSC Guru Points: 134873 More actions · Answer 3

you will need helper objects (#temptb) to solve this tree in sqlserver2000.

With sql2005 you would use a CTE.

Search SSC for "tree" and you'll find some options.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

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

Another place to look is under "Expanding Hierarchies" in Books OnLine...

--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)
Intro to Tally Tables and Functions

Petrilli Baptiste SSC Enthusiast Points: 194 More actions · Answer 5

<>

Hi, have you try something called a cartesian operation (sorry im French and i m not sure it is the good translation), actually the aim is to make all the couple possibilities and then restrict with the where clause in order to check if the original record got a subcategory, ive made a query like this but ive got hole in my brain, i will try to find it and then i will post it.