...

View Full Version : Adjacency result on same column



sleidia
02-23-2007, 06:08 PM
Hi :)

Here is 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 |
+----------------------+


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

Thanks for the help !

guelphdad
02-24-2007, 04:47 AM
You would need a UNION.

sleidia
02-24-2007, 10:36 AM
Thanks.

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



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum