group_cat question

say i got a table like this :
cat_id, name,parent. with rows
1,"Shops",0
2,"Fashion",1

is there a way to create a query that would produce me the following
result? 2,"Shops / Fashion"

On 7 Mar, 12:46, "M. K." <dilb...@i-wanna.live-spam-free.volny.cz>
wrote:
> say i got a table like this :
> cat_id, name,parent. with rows
> 1,"Shops",0
> 2,"Fashion",1
>
> is there a way to create a query that would produce me the following
> result? 2,"Shops / Fashion"

Something like:

SELECT CONCAT(t2.name,' / ',t1.name) FROM a_table t1
LEFT JOIN a_table t2 ON t1.parent = t2.cat_id
WHERE t1.cat_id = 2;

On Fri, 07 Mar 2008 18:03:13 +0100, strawberry <zac.carey@gmail.com> wrote:

> On 7 Mar, 12:46, "M. K." <dilb...@i-wanna.live-spam-free.volny.cz>
> wrote:
>> say i got a table like this :
>> cat_id, name,parent. with rows
>> 1,"Shops",0
>> 2,"Fashion",1
>>
>> is there a way to create a query that would produce me the following
>> result? 2,"Shops / Fashion"
>
> Something like:
>
> SELECT CONCAT(t2.name,' / ',t1.name) FROM a_table t1
> LEFT JOIN a_table t2 ON t1.parent = t2.cat_id
> WHERE t1.cat_id = 2;

Indeed, with a join for every possible level in hierarchy (in which case
is CONCAT_WS() is more appropriate, because one could as easily concat a
7th level node as easily as a 2th level node without having problems with
an 'overhead' of '/'s).

If the level should be / is desired to be unlimited, and the select's are
much more abundant then alterations, the nested set model could be a
solution. Harder on the inserts/updates (in hierarchy/position)/deletes,
easier on the selects. See:
<http://dev.mysql.com/tech-resources/articles/hierarchical-data.html>
--
Rik Wasmus