×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

please help! - easy quesion by can't think of it.

please help! - easy quesion by can't think of it.

please help! - easy quesion by can't think of it.

(OP)
I have a table with two columns.  First column is the parent category whereas the second column is the sub category of the first column.

In this example: the layer will go something like this.
cat_id(1) -> sub_cat_id(4) -> then sub_cat_id(4) has four more subsub categories (235, 236, 237, 310) and each one of the subsub category can have more subsubsub category and so on.

I'm look for sql statement to help
a) cat_id doesn't have sub_cat_id
b) cat_id that has sub_cat_id
c) more importantly, a way to retrieve all the sub, subsub, subsubsub,.... for each parent category

Thank you.

cat_id    sub_cat_id
1    2
1    3
1    4
1    5
1    6
1    7
1    8
1    9
1    10
1    11
1    12
1    13
1    14
1    15
1    16
1    17
1    18
1    240
1    315
4    235
4    236
4    237
4    310
5    238
5    239
6    230
6    231
6    232
6    233
6    234
7    175
7    176
7    177
7    227
7    228
7    229
7    281
9    159
9    160
9    161
9    162
11    304
11    305
13    222
13    223
13    224
13    225
13    226
19    173
19    174
19    241
19    252
19    316
19    317
20    21
20    22
20    23
20    24
20    25
20    26
20    27
28    30
28    32
28    34
28    36
40    41
40    42
40    43
40    44
40    45
40    46
40    47
40    48
40    49
40    50
40    51
40    52
40    53
40    54
40    55
40    56
40    303
40    319
47    274
47    275
49    154
49    155
49    156
49    157
49    158
57    58
57    64
57    65
57    66
57    72
57    73
57    80
57    253
57    312
57    313
58    59
58    60
58    61
58    62
58    63
58    191
58    193
58    278
64    199
64    200
64    201
64    202
64    203
64    204
64    311
65    195
65    196
65    197
65    198
66    205
66    206
66    276
67    68
67    69
67    70
73    74
73    178
73    179
81    82
81    83
81    84
81    85
81    86
82    261
82    262
82    283
82    285
83    211
83    212
87    88
87    89
87    90
87    91
87    92
87    93
87    94
87    95
87    96
87    272
87    277
97    98
97    99
97    100
97    101
97    103
97    217
97    295
98    300
98    301
98    302
99    213
99    214
99    215
99    216
99    271
99    282
99    309
99    314
101    102
101    182
101    183
101    184
101    185
101    186
101    187
101    279
101    288
105    106
105    107
105    108
105    109
105    110
105    111
105    112
105    113
105    114
105    192
105    194
105    270
105    284
105    289
105    293
105    318
113    263
113    264
113    265
113    266
115    116
115    117
115    118
115    119
115    120
115    121
122    123
122    124
122    125
122    127
122    128
122    129
122    130
122    131
122    132
122    243
122    273
125    126
125    180
125    181
125    299
130    296
130    297
130    298
133    134
133    135
133    136
133    146
133    147
133    148
133    150
133    151
133    247
136    137
136    138
136    139
136    140
136    141
136    143
136    144
136    248
136    290
136    291
136    292
147    163
147    164
147    165
147    166
147    167
147    168
147    169
147    267
147    286
147    287
148    170
148    171
148    172
151    149
151    268
151    269
218    219
218    220
242    67
242    71
242    249
242    251
247    244
247    245
247    246
249    76
249    208
249    209
249    250
249    306
249    307
249    308
251    77
251    79
251    210
251    280
254    31
254    33
254    37
254    38
254    39
254    104
254    256
254    257

RE: please help! - easy quesion by can't think of it.

You'll need to use a cursor to do this

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible.  All it takes is a little research. (Me)
noevil
http://www.mrdenny.com

RE: please help! - easy quesion by can't think of it.

If you know the deepest level of subsubsub...ids may exist you may solve it with several self joins, but it's not recommendable, as you may miss some subcategory levels.

CODE

select a.cat_id, a.sub_cat_id, b.sub_cat_id as sub2_cat_id, c.sub_cat_id as sub3_cat_id, d.sub_cat_id as sub4_cat_id ...
from table as a
left join table as b on a.sub_cat_id = b.cat_id
left join table as c on b.sub_cat_id = c.cat_id
...

If you only want to have records in the result set, where cat_id is no sub category, you'd need to add

CODE

where a.cat_id not in select distinct sub_cat_id from table

Besides the limitation of sub category levels (by the number of self joins possible) it's not very performant.

Bye, Olaf.

RE: please help! - easy quesion by can't think of it.

(OP)
Thanks for the points.  Is there some syntax that I can find out the deepest level of subsubsub.. and then you some kind of loop to create the join.

RE: please help! - easy quesion by can't think of it.

Have you tried CONNECT BY and START WITH clauses? That can report a hierarchy like this. something like...

CODE

select lpad(' ',2*(level-1)) || to_char(sub_cat_id) s
from my_table
start with cat_id = 1
connect by prior sub_cat_id = cat_id;
I haven't tested it, but if you correct my typos (bigsmile) it should give you a "graphic" view of the hierarchy.

Plenty of links online for more info...

http://www.orafaq.com/faqsql.htm#TREE
http://philip.greenspun.com/sql/trees
http://www.adp-gmbh.ch/ora/sql/connect_by.html

Hope this helps.

RE: please help! - easy quesion by can't think of it.


What do you want as an outcome?

Here is a small program to find the deepest level of subcategories (while retrieving the records themselves along the way - you can use them as you go one way or another). I understand that it's not only SQL statements, it's SQL statements within the loop. I wrote it in Visual FoxPro, because it is what I usually do, but see if you can "translate" it into whatever you need.

CODE

nFound=100
nLevel=1
dbName="Categories"

DO WHILE nFound>0
    SELECT a.sub_cat_id AS cat_id, b.sub_cat_id AS sub_cat_id ;
        FROM (dbName) AS a INNER JOIN categories AS b ;
        ON b.cat_id=a.sub_cat_id ;
        INTO CURSOR ("tmp"+STR(nLevel,1))

    nFound=_TALLY
    nLevel=IIF(nFound>0,nLevel+1,nLevel)
    dbName="tmp"+STR(nLevel,1)

ENDDO

WAIT WINDOW "Max. Level is "+STR(nLevel,1)

RE: please help! - easy quesion by can't think of it.

Hi again,

by revisting this, I think it's more natural to have a cat_id and a parent_cat_id instead of sub_cat_id? That way the categories with no parent will definately mark level 0 or 1 (depending where you begin counting).

As you have a sub_cat_id the outmost leaves (the deepest level) of the tree structure will be at one of the records with no sub_cat_id. To determine their level you'd need to find the way up to the main_cat_id from there.

Either way you can have loops within this structure, so you should set a maximum recursion level anyway, if you don't want to get stuck in an endless loop due to corrupted data, eg a category having one of it's parent- (or grandparent etc.) categories as a sub-category.

Like Stella I usually use Foxpro and as that has it's oldest root in dBase, besides XML it has another way of handling datasets as a collection of single records you can scan or seek through the way you want. Although the ORACLE syntax with connect by seems very elegant and has no need to determine the maximum level beforehand, the problem is much easier solved by using a recursive function.

Bye, Olaf.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close