my problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

my problem

Author

Topic

mr_dayal
Starting Member

37 Posts

Posted - 2008-09-08 : 07:03:35

I have a table category_master having definition like this..

cat_id bigint
parent_id bigint
cat_name varchar(50)
date_added datetime
date_modified datetime

There r few data in this table.

cat_id parent_id cat_name
3 0 COMPUTERS
5 0 PRINTERS
7 3 FLASH MEMORY
8 3 CD/DVD DRIVES
9 8 EXTERNAL CD/DVD DRIVES
12 0 APPERALS
13 12 SHIRTS
15 0 CONSUMER DURABLES
16 15 A/C
17 0 CHOCOLATES
28 12 LEATHER GOODS
29 0 CHILDREN
30 29 CHILDREN SHIRTS
31 29 CHILDREN T-SHIRTS
32 29 TOYS
33 7 MEMORY
34 15 MOBILE
35 34 CAMERA MOBILE
36 12 PEN
37 13 MEN
49 8 INTERNAL CD/DVD DRIVES

now , If I select Computers , All the categories lying under it should be listed.Like If I select 'COMPUTERS' First 'CD/DVD DRIVES' and FLASH MEMORY Should be selected and then 'INTERNAL CD/DVD DRIVES' and 'EXTERNAL CD/DVD DRIVES'should be selected since they r under 'CD/DVD DRIVES'category which is again under the main category of 'computers'.

In short If I select computers , all the sub categories at all the levels should be selected.(CD/DVD DRIVES', 'FLASH MEMORY','EXTERNAL CD/DVD DRIVES','INTERNAL CD/DVD DRIVES')

Mr Dayal

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-09-08 : 09:47:24

What version of SQL Server are you using?

Also, please provide a specific example of how you would like the results to be returned. i.e., exactly what rows/columns you are looking for.

- Jeff
http://weblogs.sqlteam.com/JeffS

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-09-08 : 12:20:27

If you are using 2005 or 2008 look in BOL for Recursive CTEs. You can also do a search on the formus here and get some other examples.

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-09-08 : 12:30:31

http://www.sitepoint.com/article/hierarchical-data-database/

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources