My Table Structure
Category_ID Number
Parent_ID Number <--Category_ID reports to this colum
Category_Name Varchar...
MY QUERY <--I replaced the query above with my data
=============================
WITH Hierarchy(Category_ID, Category_Name, Parent_ID, HLevel)
AS
(
SELECT Category_ID, Category_Name, Parent_ID, 0 as HLevel FROM Dir_Categories
UNION ALL
SELECT SubCategory.Category_ID
, SubCategory.Category_Name,
SubCategory.Parent_ID,
HLevel + 1
FROM Dir_Categories SubCategory
INNER JOIN Hierarchy ParentCategory
ON SubCategory.Parent_ID = ParentCategory.Category_ID )
SELECT Category_ID,
Category_Name = Replicate('__', HLevel) + Category_Name,
Parent_ID,
HLevel
FROM Hierarchy
My OUTPUT============
All the categories under reporting to Parent_ID 0 or continuous, then the ones reporting to 1 and so fourth. Subcategories are not showing within their main categories. I AM GOING NUTS WITH THIS.
Can you help me please?One golden rule in database systems is: Never expect the rows to appear in a specific order. If you for some reason need the columns to be in a specific order, use order by on a suitable column. Here you don't have one, sou you'll have to add a column that you can use for sorting, for instance a varchar column containing the list of IDs from root to the node.|||One golden rule in database systems is: Never expect the rows to appear in a specific order. If you for some reason need the columns to be in a specific order, use order by on a suitable column. Here you don't have one, sou you'll have to add a column that you can use for sorting, for instance a varchar column containing the list of IDs from root to the node.
Hmm, I actually have one that lists the category IDs from root to node. That is a good Ideal, I will try that and get back to you.
Thanks|||One golden rule in database systems is: Never expect the rows to appear in a specific order. If you for some reason need the columns to be in a specific order, use order by on a suitable column. Here you don't have one, sou you'll have to add a column that you can use for sorting, for instance a varchar column containing the list of IDs from root to the node.
Look at this output, look at the PathID, can you suggest how I can order that to get the subcategories inside the Categories? I am more of a web guy, and can generally do some good querying but I am better in Oracle.
Check it out:
============================
PATHID
1
10
100,3
101,3
102,3
103,3
104,3
105,3
106,3
107,3
108,3
109,3
11
110,4
111,4
112,4
113,4
114,4
115,4
116,4
117,4
118,4
119,4
12
120,4
121,4
122,4
123,4
124,4
125,4
126,4
127,4
128,4
129,4
13
130,4
131,4
132,4
133,5
134,5
135,5
136,5
137,5
138,5
139,5
14
140,5
141,5
142,5
143,5
144,5
145,5
146,5
147,5
148,5
149,5
15
150,6
151,6
152,6
153,6
154,6
155,6
156,6
157,6
158,6
159,6
16,1
160,6
161,6
162,7
163,7
164,7
165,7
166,7
167,7
168,7
169,8
17,1
170,8
171,8
172,8
173,8
174,8
175,8
176,8
177,8
178,8
179,8
18,1
180,8
181,8
182,8
183,8
184,8
185,8
186,8
187,8
189,8
19,1
190,8
191,8
192,8
193,9
194,9
195,9
196,9
197,9
198,9
199,9
2
20,1
200,9
201,9
202,9
203,9
204,10
205,10
206,10
207,10
208,10
209,10
21,1
210,10
211,10
212,10
213,10
214,10
215,10
216,11
217,11
218,11
219,11
22,1
220,11
221,11
222,11
223,11
224,11
225,11
226,11
227,11
228,11
229,11
23,16,1
230,11
231,11
232,11
233,12
234,12
235,12
236,13
237,13
238,14
239,14
24,16,1
240,14
241,14
242,14
243,14
245,14
246,14
247,14
248,14
249,14
25,16,1
250,14
252,14
253,14
254,14
255,14
256,14
257,14
258,14
259,14
26,16,1
260,14
261,14
262,14
263,14
264,14
265,14
266,14
267,14
268,14
269,14
27,16,1
270,14
271,14
272,15
273,15
274,15
275,15
276,15
277,15
278,15
279,15
28,16,1
280,15
281,15
282
283,282
284,282
285,282
286,282
287,282
288,282
289,282
29,16,1
290,282
291,282
292,282
293,282
294,282
295,282
296,86,2
297,86,2
298,86,2
299,86,2
3
30,16,1
300,86,2
301,86,2
302,86,2
303,86,2
304,86,2
305,86,2
309,87,2
31,16,1
310,87,2
311,87,2
312,87,2
313,88,2
314,88,2
315,88,2
316,88,2
317,88,2
318,88,2
319,88,2
32,16,1
320,88,2
322,88,2
323,88,2
324,89,2
325,89,2
326,89,2
327,89,2
328,89,2
329,89,2
33,16,1
330,89,2
331,89,2
332,89,2
333,89,2
334,89,2
335,89,2
336,89,2
337,89,2
338,89,2
339,89,2
34,16,1
340,90,2
341,90,2
342,90,2
343,90,2
344,90,2
345,90,2
346,90,2
347,90,2
348,90,2
349,90,2
35,17,1
350,90,2
351,90,2
352,90,2
353,92,2
354,92,2
355,92,2
356,92,2
357,92,2
358,92,2
359,92,2
36,17,1
360,92,2
361,92,2
362,92,2
363,92,2
364,92,2
365,93,2
366,93,2
367,93,2
368,93,2
369,93,2
37,17,1
370,93,2
371,93,2
372,366,93,2
373,366,93,2
374,366,93,2
375,366,93,2
376,366,93,2
377,366,93,2
378,366,93,2
379,366,93,2
38,17,1
380,366,93,2
381,366,93,2
382,366,93,2
383,366,93,2
384,366,93,2
385,366,93,2
386,366,93,2
387,366,93,2
388,366,93,2
389,366,93,2
39,17,1
390,366,93,2
391,366,93,2
392,366,93,2
393,94,2
394,94,2
395,94,2
396,94,2
397,94,2
398,94,2
399,94,2
4
40,17,1
400,94,2
401,94,2
402,94,2
403,94,2
404,94,2
405,94,2
406,95,2
407,95,2
408,95,2
409,95,2
41,17,1
410,95,2
411,95,2
412,95,2
413,95,2
414,95,2
415,95,2
416,96,2
417,96,2
418,96,2
419,96,2
42,17,1
420,96,2
421,96,2
422,96,2
423,96,2
424,96,2
425,96,2
426,96,2
427,98,2
428,98,2
429,98,2
43,17,1
430,98,2
431,98,2
432,98,2
433,98,2
434,98,2
435,98,2
436,98,2
437,98,2
438,98,2
439,98,2
44,17,1
440,110,4
441,110,4
442,110,4
443,110,4
444,110,4
445,110,4
45,17,1
450,110,4
451,110,4
452,111,4
453,111,4
454,111,4
455,111,4
456,111,4
457,111,4
458,111,4
459,111,4
46,17,1
460,111,4
461,111,4
462,111,4
463,111,4
464,111,4
465,111,4
466,111,4
467,111,4
468,111,4
469,111,4
47,17,1
470,111,4
471,112,4
472,112,4
473,112,4
474,112,4
475,111,4
476,112,4
477,112,4
478,112,4
479,112,4
48,17,1
480,112,4
481,112,4
482,112,4
483,112,4
484,112,4
485,112,4
489,109,3
49,17,1
490,109,3
491,109,3
492,109,3
493,109,3
494,109,3
495,109,3
496,109,3
497,101,3
498,101,3
499,101,3
5
50,17,1
500,101,3
501,101,3
502,101,3
503,101,3
504,101,3
505,102,3
506,102,3
507,102,3
508
509,508
51,17,1
510,508
511,508
512,508
513,508
514,508
515,508
516,508
517,508
518,508
519,508
52,17,1
520,508
521,92,2
522,521,92,2
523,521,92,2
524,521,92,2
525,521,92,2
526,521,92,2
527,521,92,2
528,521,92,2
529,521,92,2
53,17,1
530,521,92,2
531,101,3
532,537,536
533,537,536
534,537,536
535,537,536
536
537,536
538,536
539,234,12
54,17,1
540,234,12
541,234,12
542,234,12
543,234,12
545,234,12
546,234,12
547,234,12
548,282
549,2
55,17,1
550,7
56,17,1
560,3
561,312,87,2
562,130,4
563,234,12
564,130,4
565,7
566,7
567,1
568,130,4
569,7
57,17,1
570,234,12
571,234,12
572,2
573,572,2
574,234,12
58,18,1
59,18,1
6
60,19,1
61,19,1
62,19,1
63,19,1
64,19,1
65,19,1
66,19,1
67,19,1
68,19,1
69,19,1
7
70,19,1
71,20,1
72,20,1
73,20,1
74,20,1
75,20,1
76,20,1
77,20,1
78,21,1
79,21,1
8
80,21,1
81,21,1
82,21,1
83,21,1
84,22,1
85,22,1
86,2
87,2
88,2
89,2
9
90,2
92,2
93,2
94,2
95,2
96,2
97,2
98,2
99,2|||i dunno, man, you lost me somewhere around 237,13 ;)
you're a web guy? then you know that an unlimited number of levels is likely not workable?
how many users are willing to drill down fifteen levels to find where they're going?
if you can limit the hierarchy to some fixed maximum number of levels, then you can use a series of self-joins instead of a recursive query (although of course a recursive query will work with a fixed number of levels too)
perhaps this article may help: Categories and Subcategories (http://sqllessons.com/categories.html)
and yes, there is no problem with fifteen self-joins in a query
:)|||Here is the modified query, the output is still disorganized, it looks closer to what I am looking for though. Can you let me know what needs to be changed?
================================================== ======
WITH Tree(Category_ID, Parent_ID, Category_Name, Path, Hlevel)
AS
(
SELECT Category_ID, Parent_ID, Category_Name, PathID, 0 as Hlevel
FROM Dir_Categories
WHERE Parent_ID = 0
UNION ALL
SELECT e.Category_ID, e.Parent_ID, e.Category_Name, e.PathID, Hlevel + 1
FROM Dir_Categories e
INNER JOIN Tree d
ON e.Parent_ID = d.Category_ID
)
SELECT Category_ID, Parent_ID, HLevel,
Category_Name = Replicate('__', HLevel) + Category_Name, Path
FROM Tree order by Category_ID, Path;
GO|||i dunno, man, you lost me somewhere around 237,13 ;)
you're a web guy? then you know that an unlimited number of levels is likely not workable?
how many users are willing to drill down fifteen levels to find where they're going?
if you can limit the hierarchy to some fixed maximum number of levels, then you can use a series of self-joins instead of a recursive query (although of course a recursive query will work with a fixed number of levels too)
perhaps this article may help: Categories and Subcategories (http://sqllessons.com/categories.html)
and yes, there is no problem with fifteen self-joins in a query
:)
I am not going further deeper than 4 levels. It is a web directory and Search Engines dont go that deep so I keep it no more than four. Again, you are talking about self joins and all that but maybe some examples will help, I will take a look at the article. THanks|||i dunno, man, you lost me somewhere around 237,13 ;)
you're a web guy? then you know that an unlimited number of levels is likely not workable?
how many users are willing to drill down fifteen levels to find where they're going?
if you can limit the hierarchy to some fixed maximum number of levels, then you can use a series of self-joins instead of a recursive query (although of course a recursive query will work with a fixed number of levels too)
perhaps this article may help: Categories and Subcategories (http://sqllessons.com/categories.html)
and yes, there is no problem with fifteen self-joins in a query
:)
Thanks, I applied this query and although it is very useful, it doesnt produce the display that i am looking for. And quite honestly it would be too much coding to take the data and create a fabricated HTML output that looks like a tree. It is the only thing that has worked so far though.
AAAHHHHRRGGG, this is so simple in Oracle, just using Connect By. The problem is that I have a shared windows hosting and they only have SQL server. Oracle hosting is too expensive and for the purpose I am developing this, I want anyone who I extend the code to be able to use SQL shared hosting which is the most common one.
Any other resources or suggestions?|||can you show the query that you tried? because honestly, 4 self-joins is nothing complicated
and would you mind repeating what sort of display you are looking for|||can you show the query that you tried? because honestly, 4 self-joins is nothing complicated
and would you mind repeating what sort of display you are looking for
Here is the query I tried now
===================================
select root.Category_Name as root_name
, down1.category_name as down1_name
, down2.category_name as down2_name
, down3.category_name as down3_name
from Dir_categories as root
left outer
join Dir_categories as down1
on down1.parent_id = root.Category_ID
left outer
join Dir_categories as down2
on down2.parent_id = down1.category_id
left outer
join Dir_categories as down3
on down3.parent_id = down2.category_id
where root.parent_id =0
order
by root_name
, down1_name
, down2_name
, down3_name
It displays something like the below, but that is not what I am looking for. I am looking for a tree view that looks like this. Check this website out http://www.9searches.org/sitemap.asp I did this by doing four queries one inside the other, but it i s too slow. I want performance...
======================================
Business Industry Specific Automotive Auto Sales
Business Industry Specific Automotive Car Accessories
Business Industry Specific Automotive Diesel Mechanics
Business Industry Specific Automotive Exotic and Classic Vehicles
Business Industry Specific Automotive General Enhancements
Business Industry Specific Automotive High Performance
Business Industry Specific Automotive Racing
Business Industry Specific Automotive Repair and Maintenance
Business Industry Specific Automotive Road Services
Business Industry Specific Construction NULL
Business Industry Specific Energy and Utilities NULL
Business Law Branches of Law Disabilities
Business Law Branches of Law Employment
============================================
My initial query was
Here is the modified query, the output is still disorganized, it looks closer to what I am looking for though. Can you let me know what needs to be changed?
================================================== ======
WITH Tree(Category_ID, Parent_ID, Category_Name, Path, Hlevel)
AS
(
SELECT Category_ID, Parent_ID, Category_Name, PathID, 0 as Hlevel
FROM Dir_Categories
WHERE Parent_ID = 0
UNION ALL
SELECT e.Category_ID, e.Parent_ID, e.Category_Name, e.PathID, Hlevel + 1
FROM Dir_Categories e
INNER JOIN Tree d
ON e.Parent_ID = d.Category_ID
)
SELECT Category_ID, Parent_ID, HLevel,
Category_Name = Replicate('__', HLevel) + Category_Name, Path
FROM Tree order by Category_ID, Path;
GO
This query produces this output, Tree like but not within corresponding parent categories
==================================
1 0 0 Arts & Humanities
2 0 0 Business
3 0 0 Children
4 0 0 Computers
5 0 0 Education
6 0 0 Entertainment
7 0 0 Home & Garden
8 0 0 Health & Fitness
9 0 0 News & Politics
10 0 0 People & Society
11 0 0 Real Estate
12 0 0 Recreation & Sports
13 0 0 Science & Technology
14 0 0 Shopping
15 0 0 Travel
16 1 1 |__Books and Literature
17 1 1 |__Philosophy
18 1 1 |__Antiques
19 1 1 |__Arts
20 1 1 |__Humanities
21 1 1 |__Museums
22 1 1 |__Organizations
23 16 2 |__|__Art Books
24 16 2 |__|__Authors
25 16 2 |__|__Banned Books
26 16 2 |__|__Best-Seller Lists
27 16 2 |__|__Bookstores and Booksellers
28 16 2 |__|__Book Reading Groups
29 16 2 |__|__Downloadable Texts
30 16 2 |__|__By Genre
31 16 2 |__|__Magazines and e-Zines
32 16 2 |__|__Periods and Movements
33 16 2 |__|__Publishers
34 16 2 |__|__Reviews
35 17 2 |__|__Academic Departments
36 17 2 |__|__Ancient Greek Philosophy
37 17 2 |__|__Axiology
38 17 2 |__|__Chats and Forums
39 17 2 |__|__Chinese Philosophy
40 17 2 |__|__Continental Philosophy
41 17 2 |__|__Current Movements
42 17 2 |__|__Eastern
43 17 2 |__|__Epistemology
44 17 2 |__|__Ethics
45 17 2 |__|__German Idealism
46 17 2 |__|__History
47 17 2 |__|__Humanism
48 17 2 |__|__Journals
49 17 2 |__|__Logic
50 17 2 |__|__Metaphysics
51 17 2 |__|__New Age
52 17 2 |__|__Organizations
53 17 2 |__|__Personal Pages
54 17 2 |__|__Philosophers
55 17 2 |__|__Philosophy of Religion
56 17 2 |__|__Philosophy of Science
57 17 2 |__|__Reference
58 18 2 |__|__Antique Jewelry
59 18 2 |__|__Restoration
60 19 2 |__|__Animation
61 19 2 |__|__Arts Publications
62 19 2 |__|__Arts Resources
63 19 2 |__|__Art Exhibits
64 19 2 |__|__Cartoons
65 19 2 |__|__Design Arts
66 19 2 |__|__Education
67 19 2 |__|__Museums
68 19 2 |__|__Performing Arts
69 19 2 |__|__Visual Arts
70 19 2 |__|__Writers Resources
71 20 2 |__|__Books and Literature
72 20 2 |__|__Classical Studies
73 20 2 |__|__Cultural Studies
74 20 2 |__|__History
75 20 2 |__|__Languages
76 20 2 |__|__Linguistics
77 20 2 |__|__Philosophy
78 21 2 |__|__Maritime Museums
79 21 2 |__|__Railway Museums
80 21 2 |__|__Aviation Museums
81 21 2 |__|__Children Museums
82 21 2 |__|__Science Museums
83 21 2 |__|__Museums
84 22 2 |__|__National Endowment for the Arts
85 22 2 |__|__National Endowment for the Humanities
86 2 1 |__Comodities
87 2 1 |__Computing
88 2 1 |__Finance
89 2 1 |__Global Commerce and Government
90 2 1 |__Human Resources
92 2 1 |__Industry Specific
93 2 1 |__Law
94 2 1 |__Management
95 2 1 |__News and Resources
96 2 1 |__Non Profit
97 2 1 |__Professional Associations and Organizations
98 2 1 |__Sales and Marketing
99 2 1 |__Small Business
100 3 1 |__Toys
101 3 1 |__Family Entertainment
102 3 1 |__Health
103 3 1 |__Pets and Animals
104 3 1 |__Recreation
105 3 1 |__Schools
106 3 1 |__Sports
107 3 1 |__Nutrition
108 3 1 |__Children Books
109 3 1 |__Computer Related
110 4 1 |__Algorithms
111 4 1 |__Artificial Intelligence|||okay, try this --select root.Category_Name as root_name
, down1.Category_Name as down1_name
, down2.Category_Name as down2_name
, down3.Category_Name as down3_name
from Dir_categories as root
inner
join Dir_categories as down1
on down1.Parent_ID = root.Category_ID
inner
join Dir_categories as down2
on down2.Parent_ID = down1.Category_ID
inner
join Dir_categories as down3
on down3.Parent_ID = down2.Category_ID
where root.Parent_ID = 0
UNION ALL
select root.Category_Name
, down1.Category_Name
, down2.Category_Name
, null
from Dir_categories as root
inner
join Dir_categories as down1
on down1.Parent_ID = root.Category_ID
inner
join Dir_categories as down2
on down2.Parent_ID = down1.Category_ID
where root.Parent_ID = 0
UNION ALL
select root.Category_Name
, down1.Category_Name
, null
, null
from Dir_categories as root
inner
join Dir_categories as down1
on down1.Parent_ID = root.Category_ID
where root.Parent_ID = 0
UNION ALL
select root.Category_Name
, null
, null
, null
from Dir_categories as root
where root.Parent_ID = 0
order
by root_name
, down1_name
, down2_name
, down3_name|||It is just the output. it does not look like a |__TREE
|__LEAF
View|||okay, you are right, it does not show the |__ marks
note that you need the first 4 columns of the result set in order to sort the results properly
now we add one more column -- a concatenation of your marks, along with the name of the node
so what you will do is run the following query, but display only the 5th column as your hierarchy
select root.Category_Name as root_name
, down1.Category_Name as down1_name
, down2.Category_Name as down2_name
, down3.Category_Name as down3_name
, '|_|_|_' + down3.Category_Name as data_to_display
from Dir_categories as root
inner
join Dir_categories as down1
on down1.Parent_ID = root.Category_ID
inner
join Dir_categories as down2
on down2.Parent_ID = down1.Category_ID
inner
join Dir_categories as down3
on down3.Parent_ID = down2.Category_ID
where root.Parent_ID = 0
UNION ALL
select root.Category_Name
, down1.Category_Name
, down2.Category_Name
, null
, '|_|_' + down2.Category_Name as data_to_display
from Dir_categories as root
inner
join Dir_categories as down1
on down1.Parent_ID = root.Category_ID
inner
join Dir_categories as down2
on down2.Parent_ID = down1.Category_ID
where root.Parent_ID = 0
UNION ALL
select root.Category_Name
, down1.Category_Name
, null
, null
, '|_' + down1.Category_Name as data_to_display
from Dir_categories as root
inner
join Dir_categories as down1
on down1.Parent_ID = root.Category_ID
where root.Parent_ID = 0
UNION ALL
select root.Category_Name
, null
, null
, null
, root.Category_Name as data_to_display
from Dir_categories as root
where root.Parent_ID = 0
order
by root_name
, down1_name
, down2_name
, down3_name
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment