Adjacency result on same column 1

[Sleidia]

Another question about adjacency :

In

http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

, there is the following table structure :

+-------------+----------------------+--------+
| 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 :

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 :

+-------------+----------------------+-------------+-------+
| lev1        | lev2                 | lev3        | lev4  |
+-------------+----------------------+-------------+-------+
| ELECTRONICS | PORTABLE ELECTRONICS | MP3 PLAYERS | FLASH |
+-------------+----------------------+-------------+-------+

But, is there a query that could produce this? :

+----------------------+
| all levels           |
+----------------------+
| ELECTRONICS          |
+----------------------+
| PORTABLE ELECTRONICS |
+----------------------+
| MP3 PLAYERS          |
+----------------------+
| FLASH                |
+----------------------+
| ELECTRONICS          |
+----------------------+

Basically, I need to know how to output all the subcategories in a single column.

Thanks for the help !

 

[r937]

  SELECT t1.name AS "all levels"
  FROM category AS t1
  WHERE t1.name = 'ELECTRONICS'
union all
  SELECT t2.name 
  FROM category AS t1
  INNER JOIN category AS t2 ON t2.parent = t1.category_id
  WHERE t1.name = 'ELECTRONICS' 
union all
  SELECT t3.name 
  FROM category AS t1
  INNER JOIN category AS t2 ON t2.parent = t1.category_id
  INNER JOIN category AS t3 ON t3.parent = t2.category_id
  WHERE t1.name = 'ELECTRONICS'
union all
  SELECT t4.name 
  FROM category AS t1
  INNER JOIN category AS t2 ON t2.parent = t1.category_id
  INNER JOIN category AS t3 ON t3.parent = t2.category_id
  INNER JOIN category AS t4 ON t4.parent = t3.category_id
  WHERE t1.name = 'ELECTRONICS' AND t4.name = 'FLASH';

r937.com | rudy.ca

 

[Sleidia]

Thanks Rudy !

Seriously, looking at it, I'd never have been able to find it by myself, even with the help of the documentation.

That was a great help for me.