Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New Coder
    Join Date
    Aug 2006
    Posts
    50
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Adjacency result on same column

    Hi

    Here is question about adjacency :

    In http://dev.mysql.com/tech-resources/...ical-data.html , there is the following table structure :

    Code:
    +-------------+----------------------+--------+
    | category_id | name                 | parent |
    +-------------+----------------------+--------+
    |           1 | ELECTRONICS          |   NULL |
    |           2 | TELEVISIONS          |      1 |
    |           3 | TUBE                 |      2 |
    |           4 | LCD                  |      2 |
    |           5 | PLASMA               |      2 |
    |           6 | PORTABLE ELECTRONICS |      1 |
    |           7 | MP3 PLAYERS          |      6 |
    |           8 | FLASH                |      7 |
    |           9 | CD PLAYERS           |      6 |
    |          10 | 2 WAY RADIOS         |      6 |
    +-------------+----------------------+--------+
    .. and the query under "Retrieving a Single Path" is this :

    Code:
    SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4
    FROM category AS t1
    LEFT JOIN category AS t2 ON t2.parent = t1.category_id
    LEFT JOIN category AS t3 ON t3.parent = t2.category_id
    LEFT JOIN category AS t4 ON t4.parent = t3.category_id
    WHERE t1.name = 'ELECTRONICS' AND t4.name = 'FLASH';
    ... which produces this :

    Code:
    +-------------+----------------------+-------------+-------+
    | lev1        | lev2                 | lev3        | lev4  |
    +-------------+----------------------+-------------+-------+
    | ELECTRONICS | PORTABLE ELECTRONICS | MP3 PLAYERS | FLASH |
    +-------------+----------------------+-------------+-------+
    But, is there a query that could produce this? :

    Code:
    +----------------------+
    | all levels           |
    +----------------------+
    | ELECTRONICS          |
    +----------------------+
    | PORTABLE ELECTRONICS |
    +----------------------+
    | MP3 PLAYERS          |
    +----------------------+
    | FLASH                |
    +----------------------+
    Basically, I need to know how to output all the subcategories in a single column.

    Thanks for the help !

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,631
    Thanks
    4
    Thanked 147 Times in 138 Posts
    You would need a UNION.

  • #3
    New Coder
    Join Date
    Aug 2006
    Posts
    50
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Thanks.

    For those who are interested, someone cared enough to give the full query here :
    http://www.tek-tips.com/viewthread.cfm?qid=1338363


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •