sql code to retrieve info from table with nested levels of hierarchy


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 10 of 10

Thread: sql code to retrieve info from table with nested levels of hierarchy

  1. #1
    Join Date
    Apr 2008
    Posts
    5

    sql code to retrieve info from table with nested levels of hierarchy

    i have the following table:

    Code:
    [category]
    category_id
    category_name
    parent_id
    ...with many nested levels of hierarchy.

    i (desperately) need help in writing sql code which will extract every book title from the [book] table keeping in mind both the [category] and book tables are related via the "category_id" field.

    any assistance will be MUCH appreciated.

    cheers!

    ;-D

  2. #2
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    Welcome to DevX

    All of the book titles are in one table, correct?

    If all you want are the titles, then all you have to do is run a single SELECT query against that one table.
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  3. #3
    Join Date
    Apr 2008
    Posts
    5
    hey [Hack] cheers for the swift response!

    ok, i understand that part, but how do i retrieve ALL the corresponding categories and subcategories down 5 levels?

    allow me to explain:

    i have a number of nested categories as follows:

    category: data
    subcategory1: data
    subcategory2: data
    subcategory3: data
    subcategory4: data

    there may also be a situation whereby not all subcategories have data, so you may have the following scenario:

    category: data
    subcategory1: data
    subcategory2: data
    subcategory3: data
    subcategory4: no data

    category id category_title parent_id
    1 kids 0
    2 adults 0
    3 modern 1
    4 literature 1
    5 data_1 3
    6 data_2 5
    7 data_3 6

    ...so if we look at the above table we have the following data:

    category: kids
    subcategory1: modern
    subcategory2: data_1
    subcategory3: data_2
    subcategory4: data_3

    incidentally, i previosuly had each of the subcategories in separate tables but soon found out that this was not the most ideal way to go about things!

    so any further assistance will be MUCH appreciated.

    cheers

    ;-D

  4. #4
    Join Date
    Feb 2008
    Posts
    162
    You can join the table to itself like:

    Code:
    SELECT 
      c.category_name,
      sc.category_name subcategory_name
    FROM category c
    LEFT JOIN category sc ON sc.category_id = c.parent_id
    The problem is that you will need a column to sort on if you want it to be in order by subcategory level. Otherwise, all you have is alpha sorting of the category names, which probably isn't the order you want.

    On a side note, normally table names should be plural and column names are singular. ie - Your table is a list of categories and each record is an individual category.
    It's not what you don't know that gets you in trouble. It's what you know that just ain't so. -Mark Twain

  5. #5
    Join Date
    Apr 2008
    Posts
    5
    cheers [slope] for your response.

    i have struggled with sql to get to this stage and am dearly appreciative for ALL the help i have received.

    now, i completely forgot to mention that i need to extract the details of every book from every category (and nested subcategories as outlined in my last post above)in the database, assuming the [book] table and the [category] table are related via the "category_id".

    Code:
    [book]
    book_id
    book_title
    category_id
    
    [category]
    category_id
    category_title
    parent_id
    i am so sorry for this ommission.

    any further assistance will be MUCH appreciated

    cheers

    ;-D

    p.s. if it helps, when i originally had separate tables for each subcategory, i was using the following sql code:

    Code:
    SELECT
    category.*,
    subcategory1.*,
    subcategory2.*,
    subcategory3.*,
    subcategory4.*,
    author.*,
    book.*
    
    FROM
    ((((((book
    LEFT JOIN category ON category.category_id=book.category_id)
    LEFT JOIN subcategory1 ON subcategory1.category_id=category.category_id)
    LEFT JOIN subcategory2 ON subcategory2.subcategory1_id=subcategory1.subcategory1_id)
    LEFT JOIN subcategory3 ON subcategory3.subcategory2_id=subcategory2.subcategory2_id)
    LEFT JOIN subcategory4 ON subcategory4.subcategory3_id=subcategory3.subcategory3_id)
    LEFT JOIN author ON author.author_id=book.author_id)
    
    WHERE
    book.book_id = #URL.ID#
    ...but the ony problem i had here was that or each of the subcategories i was only getting the first item in the corresponding list!
    Last edited by tudor; 04-13-2008 at 07:03 AM.

  6. #6
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    What are you NOT getting from running the query Slope posted?
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  7. #7
    Join Date
    Apr 2008
    Posts
    5
    What are you NOT getting from running the query Slope posted?
    firstly, i don't understand it, and secondly, because of my error in not mentioning about the [book] table i really need the sql code which will give me the details of every book from every category (and nested subcategories as outlined in my post above)in the database, assuming the [book] table and the [category] table are related via the "category_id".

    cheers or your continuous help.

  8. #8
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    Slope's code is a pretty basic SQL query, so I'm not real sure what there is about it to not understand. Perhaps if you mentioned a specific line or something I could explain.

    The query that you posted in Post #5 is more selective actually, and I think the reason that is not bringing back everything is because of your WHERE clause. If you want ALL records then there is no reason for a WHERE clause. Note that Slope did not use one.

    What do you get if you run your query with no WHERE clause?
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  9. #9
    Join Date
    Apr 2008
    Posts
    5
    The query that you posted in Post #5 is more selective actually, and I think the reason that is not bringing back everything is because of your WHERE clause
    hey [hack] your swift responses ae REALLY appreciated, cheers!

    ok, the reason why i need a WHERE clause is when i am actually viewing the current book title, hence:

    Code:
    WHERE
    book.book_id = #URL.ID#
    ...but that was when i was doing things the wrong way with all those subcategory tables, and even then the sql code would only return the first subcategory for each level which was not corresponding to the each book!

    ok, so lets say i use [slope]'s sql code:

    Code:
    SELECT 
      c.category_name,
      sc.category_name subcategory_name
    FROM category c
    LEFT JOIN category sc ON sc.category_id = c.parent_id
    how can i get the WHERE clause in there which will equate to:

    Code:
    WHERE
    book.book_id = #URL.ID#
    ...cos i just don't understand what's going on with his code here:

    "c." and "sc."

    cheers

  10. #10
    Join Date
    Feb 2008
    Posts
    162
    Quote Originally Posted by tudor

    ...cos i just don't understand what's going on with his code here:

    "c." and "sc."

    cheers

    You are selecting from the same table twice and giving it two different aliases. I used "c" for categories and "sc" for subcategories. So, in one case the query gets the category name from the table and in the other case the category name is actually the subcategory name. That is why the column is aliased as "subcategory_name".

    I think this should give you want you want. Although, what you will get here is if you have five category/subcategory levels, you will get five records for the same book title with the five subcategories listed next to it on each respective row.

    Code:
    SELECT 
      b.book_title,
      c.category_name,
      sc.category_name subcategory_name
    FROM category c
    LEFT JOIN category sc ON sc.category_id = c.parent_id
    JOIN book b ON b.category_id = c.category_id
    WHERE b.book_id = #URL.ID#
    But again, this is pseudo code as I don't have a database to try it on here at home. It should give you something to play with and modify to get what you need.
    It's not what you don't know that gets you in trouble. It's what you know that just ain't so. -Mark Twain

Similar Threads

  1. Replies: 5
    Last Post: 04-09-2006, 08:28 PM
  2. Replies: 1
    Last Post: 02-28-2005, 10:26 AM
  3. Replies: 16
    Last Post: 04-26-2002, 02:35 PM
  4. Temporary table in SQL 7.0
    By bogus in forum Database
    Replies: 1
    Last Post: 03-22-2001, 08:26 AM
  5. GOSUB vs. Macros vs. Nested Functions
    By Jonathan Allen in forum .NET
    Replies: 331
    Last Post: 03-19-2001, 08:00 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center