SQL Server Performance

HELP, cannot get my recursive query to show TREE

Discussion in 'Performance-Related Article Discussions' started by udelojf, Mar 16, 2007.

  1. udelojf New Member

    I have been having issues with this query.

    My table has

    Category_ID <-Reporting to a Parent ID
    Parent_ID
    Category Name

    The query I am using is a copy of yours
    ================================================

    WITH Tree(Category_ID, Parent_ID, Category_Name, Hlevel)
    AS
    (
    SELECT Category_ID, Parent_ID, Category_Name, 0 as Hlevel
    FROM Dir_Categories
    WHERE Parent_ID = 0
    UNION ALL
    SELECT e.Category_ID, e.Parent_ID, e.Category_Name, 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
    FROM Tree ;
    GO

    Here is the output from HELL (Sorry, it's Killing me), I abreviated the headers below so it canfit better
    It may look like a Tree View, but the subcategories are not inside their corresponding categories. HELP PLEASE I CANT SLEEP!!!
    =======================================
    CatID ParID Level Category Name
    -----------------------------------------
    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
    282 0 0 Weblogs
    508 0 0 Lifestyle & Relationships
    536 0 0 Home & Family
    537 536 1 |__Food & Drink
    538 536 1 |__Family Entertainment
    532 537 2 |__|__Drinks and Drinking
    533 537 2 |__|__Alcoholic Beverages
    534 537 2 |__|__Non-Alcoholic Beverages
    535 537 2 |__|__Home Recipes
    509 508 1 |__Personal Development
    510 508 1 |__Alternative Lifestyle
    511 508 1 |__Cyber Relationships
    512 508 1 |__Dating
    513 508 1 |__Divorce
    514 508 1 |__Friendship
    515 508 1 |__Help and Advice
    516 508 1 |__Chats and Forums
    517 508 1 |__Love and Romance
    518 508 1 |__Marriage
    519 508 1 |__Weddings
    520 508 1 |__Singles
    283 282 1 |__Business Blogs
    284 282 1 |__Celebrity Blogs
    285 282 1 |__Entertainment Blogs
    286 282 1 |__Media Blogs
    287 282 1 |__Personal Blogs
    288 282 1 |__Political Blogs
    289 282 1 |__SfiFi Blogs
    290 282 1 |__Travel Blogs
    291 282 1 |__Sports Blogs
    292 282 1 |__Weblog Awards
    293 282 1 |__Weblog Directories
    294 282 1 |__Weblog Hosting
    295 282 1 |__Weblog Tools
    548 282 1 |__SEO & Web Marketing Blogs
    272 15 1 |__Accommodations
    273 15 1 |__Agents and Services
    274 15 1 |__Budget Travel
    275 15 1 |__Business Travels
    276 15 1 |__Destination Guides
    277 15 1 |__Trip Planning
    278 15 1 |__Maps
    279 15 1 |__News and Media
    280 15 1 |__Transportation
    281 15 1 |__Travelogues
    238 14 1 |__Apparell
    239 14 1 |__Arts & Collectibles
    240 14 1 |__Auctions
    241 14 1 |__Babies
    242 14 1 |__Beauty
    243 14 1 |__Books and Magazines
    245 14 1 |__Business Supplies
    246 14 1 |__Beauty Supplies
    247 14 1 |__Children
    248 14 1 |__Classified Ads
    249 14 1 |__Comparison Shopping
    250 14 1 |__Computers
    252 14 1 |__Consumer Electronics
    253 14 1 |__Consumer Information
    254 14 1 |__Flowers
    255 14 1 |__Food and Drink
    256 14 1 |__Freebies
    257 14 1 |__Furniture
    258 14 1 |__Garden
    259 14 1 |__General Merchandise
    260 14 1 |__Gifts and Holidays
    261 14 1 |__Health and Fitness
    262 14 1 |__Hobbies and Crafts
    263 14 1 |__Home
    264 14 1 |__Jewelry
    265 14 1 |__Membership Waterhouse Clubs
    266 14 1 |__Outdoors
    267 14 1 |__Wedding
    269 14 1 |__Vehicles
    268 14 1 |__Religious Products
    270 14 1 |__Toys and Games
    271 14 1 |__Other
    236 13 1 |__Science
    237 13 1 |__Technology
    235 12 1 |__Athlete Information
    233 12 1 |__Recreation
    234 12 1 |__Sports
    539 234 2 |__|__Golf
    540 234 2 |__|__Basketball
    541 234 2 |__|__Baseball
    542 234 2 |__|__Sport Collectibles
    543 234 2 |__|__Football
    545 234 2 |__|__Soccer
    546 234 2 |__|__Sport Fan Clubs
    547 234 2 |__|__News & Publications
    563 234 2 |__|__Fishing
    571 234 2 |__|__Air Sports
    570 234 2 |__|__Tennis
    574 234 2 |__|__Horse Racing
    216 11 1 |__Agents
    217 11 1 |__Appraisers
    218 11 1 |__Brokers
    219 11 1 |__Commercial
    220 11 1 |__Construction
    221 11 1 |__Consultants
    222 11 1 |__Foreclosures
    223 11 1 |__For Sale by Owner
    224 11 1 |__Home Builders
    225 11 1 |__Inspectors
    226 11 1 |__Legal
    227 11 1 |__Masterplan Communities
    228 11 1 |__Property Search
    229 11 1 |__Relocation Services
    230 11 1 |__Schools and Courses
    231 11 1 |__Rentals
    232 11 1 |__Buying or Selling a Home
    204 10 1 |__Communities and Groups
    205 10 1 |__Connecting With People
    206 10 1 |__Cultures
    207 10 1 |__Genealogy
    208 10 1 |__Military
    209 10 1 |__Off The Beaten Path
    210 10 1 |__Who is Who
    211 10 1 |__Spirituality and Religion
    212 10 1 |__Society
    213 10 1 |__Men
    214 10 1 |__Women
    215 10 1 |__Youth
    193 9 1 |__Aggregators and Summaries
    194 9 1 |__News
    195 9 1 |__Politics
    196 9 1 |__Ezines
    197 9 1 |__Internet Broadcast
    198 9 1 |__Nespapers and Periodicals
    199 9 1 |__Services
    200 9 1 |__Community
    201 9 1 |__Education
    202 9 1 |__Television
    203 9 1 |__Weather
    169 8 1 |__Aging
    170 8 1 |__Alternative Medicine
    171 8 1 |__Beauty
    172 8 1 |__Child Health
    173 8 1 |__Dental Health
    174 8 1 |__Dieting and Weight Loss
    175 8 1 |__Diseases and Desorders
    176 8 1 |__Drugs and Medicines
    177 8 1 |__Health News
    178 8 1 |__Health Professions
    179 8 1 |__Hospitals
    180 8 1 |__Fitness and Exercise
    181 8 1 |__Education
    182 8 1 |__Medical Organizations
    183 8 1 |__Medical Practice
    184 8 1 |__Medical Specialists
    185 8 1 |__Public Health
    186 8 1 |__Nutrition
    187 8 1 |__Procedures and Therapies
    189 8 1 |__Women Health
    190 8 1 |__Men Health
    191 8 1 |__Injuries
    192 8 1 |__Sexual Health
    162 7 1 |__Arts and Craft
    163 7 1 |__Do It Yourself
    164 7 1 |__Gardening
    165 7 1 |__Homemaking
    166 7 1 |__Interior Design
    167 7 1 |__Pest Control
    168 7 1 |__Pets and Animals
    566 7 1 |__Home Improvement
    550 7 1 |__Home Security
    569 7 1 |__Home Safety & Prevention
    565 7 1 |__Flooring
    150 6 1 |__Entertainment Reviews
    151 6 1 |__Celebrities
    152 6 1 |__Animation
    153 6 1 |__Games
    154 6 1 |__Gossip
    155 6 1 |__Horror
    156 6 1 |__Humor
    157 6 1 |__Interactive Websites
    158 6 1 |__Movies
    159 6 1 |__Music
    160 6 1 |__Radio
    161 6 1 |__Television
    133 5 1 |__Adult Education
    134 5 1 |__Directories
    135 5 1 |__Distance Learning
    136 5 1 |__Early Education
    137 5 1 |__Educational Software
    138 5 1 |__Fields of Study
    139 5 1 |__Financial Aid
    140 5 1 |__Graduation
    141 5 1 |__Home Schooling
    142 5 1 |__Internships
    143 5 1 |__K-12_Education
    144 5 1 |__Tests and Testing
    145 5 1 |__Libraries and Resources
    146 5 1 |__Educational Material
    147 5 1 |__References
    148 5 1 |__Schools
    149 5 1 |__General Resources
    130 4 1 |__Internet
    131 4 1 |__Intranet
    132 4 1 |__Networking
    110 4 1 |__Algorithms
    111 4 1 |__Artificial Intelligence
    112 4 1 |__Artificial Life
    113 4 1 |__Computer Jobs
    114 4 1 |__CAD
    115 4 1 |__Companies
    116 4 1 |__Graphics
    117 4 1 |__Education
    118 4 1 |__Data Communications
    119 4 1 |__Hacking
    120 4 1 |__Hardware
    121 4 1 |__Software
    122 4 1 |__Consulting
    123 4 1 |__Support and Repair
    124 4 1 |__Multimedia
    125 4 1 |__Publications
    126 4 1 |__Virtual Reality
    127 4 1 |__Robotics
    128 4 1 |__Security and Protection
    129 4 1 |__Tutorials and Guides
    471 112 2 |__|__Agents
    472 112 2 |__|__Art
    473 112 2 |__|__Artificial Words
    474 112 2 |__|__Biomimicry
    476 112 2 |__|__Conferences and Events
    477 112 2 |__|__Departments
    478 112 2 |__|__Distributed Projects
  2. satya Moderator

    Can you explain a bit more on issues you are having>

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

Share This Page