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

[isdex1]

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

 

[mrdenny]

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)

http://www.mrdenny.com

 

[Olaf Doschke]

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.

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

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.

 

[isdex1]

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.

 

[SamBones]

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

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 () 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.

 

[Stella740pl]

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.

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)

 

[Olaf Doschke]

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.