Complex query on tree structure: Help!

I have a table called 'category'

>show fields in category;
+-------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| parent | int(11) | YES | | NULL | |
| name | char(64) | YES | | NULL | |
| display | enum('yes','no') | YES | | NULL | |
| description | text | YES | | NULL | |
+-------------+------------------+------+-----+---------+----------------+

This forms a tree structure under which items are listed. It is never
more than 5 levels deep.
The top level is defined by the parent field being zero or null.

Now, I can generate a list of categories of interest easily enough by a
sub select statement to another table

e.g.

>select distinct category from product where product.supplier='1' and
not isnull(product.category);
+----------+
| category |
+----------+
| 10 |
| 11 |
| 12 |
| 13 |
| 14 |
| 15 |
| 16 |
| 17 |
| 48 |
| 64 |
| 66 |
| 97 |
| 119 |
| 127 |
+----------+
14 rows in set (0.00 sec)



What I want to do, is generate a distinct list of all these *and* their
parents and grandparents etc. up to the top level where the parent is
zero or null.

I'm stumped.

I could do it in code..but I'd rather do it as a single query.

On 26 Nov, 14:29, The Natural Philosopher <t...@invalid.invalid>
wrote:
> I have a table called 'category'
>
> *>show fields in category;
> +-------------+------------------+------+-----+---------+----------------+
> | Field * * * | Type * * * * * * | Null | Key | Default| Extra * * * * *|
> +-------------+------------------+------+-----+---------+----------------+
> | id * * * * *| int(11) * * * * *| NO * | PRI | NULL * *| auto_increment |
> | parent * * *| int(11) * * * * *| YES *| * * | NULL * *| * * * * * * * *|
> | name * * * *| char(64) * * * * | YES *| * * | NULL * *| * * * * * * * *|
> | display * * | enum('yes','no') | YES *| * * | NULL * *| * * * * * * * *|
> | description | text * * * * * * | YES *| * * | NULL * *| * * * * * * * *|
> +-------------+------------------+------+-----+---------+----------------+
>
> This forms a tree structure under which items are listed. It is never
> more than 5 levels deep.
> The top level is defined by the parent field being zero or null.
>
> Now, I can generate a list of categories of interest easily enough by a
> sub select statement to another table
>
> e.g.
>
> *>select distinct category from product where product.supplier='1' and
> not isnull(product.category);
> +----------+
> | category |
> +----------+
> | * * * 10 |
> | * * * 11 |
> | * * * 12 |
> | * * * 13 |
> | * * * 14 |
> | * * * 15 |
> | * * * 16 |
> | * * * 17 |
> | * * * 48 |
> | * * * 64 |
> | * * * 66 |
> | * * * 97 |
> | * * *119 |
> | * * *127 |
> +----------+
> 14 rows in set (0.00 sec)
>
> What I want to do, is generate a distinct list of all these *and* their
> parents and grandparents etc. up to the top level where the parent is
> zero or null.
>
> I'm stumped.
>
> I could do it in code..but I'd rather do it as a single query.

I've posted this list here many times. The last one is very good, but
the other 2 are worth reading too.

Captain Paralytic wrote:
> On 26 Nov, 14:29, The Natural Philosopher <t...@invalid.invalid>
> wrote:
>> I have a table called 'category'
>>
>> >show fields in category;
>> +-------------+------------------+------+-----+---------+----------------+
>> | Field | Type | Null | Key | Default | Extra |
>> +-------------+------------------+------+-----+---------+----------------+
>> | id | int(11) | NO | PRI | NULL | auto_increment |
>> | parent | int(11) | YES | | NULL | |
>> | name | char(64) | YES | | NULL | |
>> | display | enum('yes','no') | YES | | NULL | |
>> | description | text | YES | | NULL | |
>> +-------------+------------------+------+-----+---------+----------------+
>>
>> This forms a tree structure under which items are listed. It is never
>> more than 5 levels deep.
>> The top level is defined by the parent field being zero or null.
>>
>> Now, I can generate a list of categories of interest easily enough by a
>> sub select statement to another table
>>
>> e.g.
>>
>> >select distinct category from product where product.supplier='1' and
>> not isnull(product.category);
>> +----------+
>> | category |
>> +----------+
>> | 10 |
>> | 11 |
>> | 12 |
>> | 13 |
>> | 14 |
>> | 15 |
>> | 16 |
>> | 17 |
>> | 48 |
>> | 64 |
>> | 66 |
>> | 97 |
>> | 119 |
>> | 127 |
>> +----------+
>> 14 rows in set (0.00 sec)
>>
>> What I want to do, is generate a distinct list of all these *and* their
>> parents and grandparents etc. up to the top level where the parent is
>> zero or null.
>>
>> I'm stumped.
>>
>> I could do it in code..but I'd rather do it as a single query.
>
> I've posted this list here many times. The last one is very good, but
> the other 2 are worth reading too.


I've written the answer to life the universe and everything too.

You should read it.

And how pray, am I supposed to find something you posted a year ago? Google?

"The Natural Philosopher" <tnp@invalid.invalid> wrote in message
news:hem3cf$j0l$1@news.albasani.net...
>I have a table called 'category'

[..]

> I'm stumped.
>
> I could do it in code..but I'd rather do it as a single query.

I don't have much experience in this area, but I do have a couple of links
in my bookmarks just in case someone springs the requirement for
hierarchical data on me at short notice... ;-)

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

http://articles.sitepoint.com/articl...ata-database/1

I have read them at (what feels like) some distant point in the past, and I
must've considered them to be of a certain quality otherwise I wouldn't've
bothered bookmarking them.

Hopefully, they'll point you in the right direction.

A.

Andrew C. wrote:
> "The Natural Philosopher" <tnp@invalid.invalid> wrote in message
> news:hem3cf$j0l$1@news.albasani.net...
>> I have a table called 'category'
>
> [..]
>
>> I'm stumped.
>>
>> I could do it in code..but I'd rather do it as a single query.
>
> I don't have much experience in this area, but I do have a couple of links
> in my bookmarks just in case someone springs the requirement for
> hierarchical data on me at short notice... ;-)
>
> http://dev.mysql.com/tech-resources/...ical-data.html
>
> http://articles.sitepoint.com/articl...ata-database/1
>
> I have read them at (what feels like) some distant point in the past, and I
> must've considered them to be of a certain quality otherwise I wouldn't've
> bothered bookmarking them.
>
> Hopefully, they'll point you in the right direction.
>
> A.
>
>
Many thanks Andrew, but I already had those!

No, after 4 hours of beating my brains I ended up doing it the long way,
which took ten minutes to code!


I ended up with a two dimensional matrix, and simply walked through that
inserting numbers that are not null, and not already in, into a PHP array.

$result=mysql_query(sprintf(
"select distinct t1.id as a, t2.id as b,t3.id as c, t4.id as d,
t5.id as e, category.id as f
from category
left join category as t5 on t5.id=category.parent
LEFT join category AS t4 on t4.id = t5.parent
LEFT JOIN category AS t3 ON t3.id = t4.parent
LEFT JOIN category AS t2 ON t2.id = t3.parent
LEFT JOIN category AS t1 ON t1.id = t2.parent
where category.id in
(select distinct category from product where product.supplier='%d' and
not isnull(product.category))
group by a, b, c, d, e, f",$supplier));
$rows=mysql_numrows($result);
$categories=array();
for ($i=0;$i<$rows;$i++)
{
$v=mysql_result($result,$i,'a');
if ($v>0 and !in_array($v,$categories)) $categories[]=$v;
$v=mysql_result($result,$i,'b');
if ($v>0 and !in_array($v,$categories)) $categories[]=$v;
$v=mysql_result($result,$i,'c');
if ($v>0 and !in_array($v,$categories)) $categories[]=$v;
$v=mysql_result($result,$i,'d');
if ($v>0 and !in_array($v,$categories)) $categories[]=$v;
$v=mysql_result($result,$i,'e');
if ($v>0 and !in_array($v,$categories)) $categories[]=$v;
$v=mysql_result($result,$i,'f');
if ($v>0 and !in_array($v,$categories)) $categories[]=$v;
}
$category_string=implode(',',$categories);

Not as elegant as I would like, but heck, I am an engineer, not a poet!

That gave me all the categories that contained a product whose supplier
was the one in question, and allowed me to use that as a qualifying
string to walk through the tree on a supplier-specific basis.

Unless anyone can do it better, that's my solution for now!

"The Natural Philosopher" <tnp@invalid.invalid> wrote in message
news:hem3cf$j0l$1@news.albasani.net...
>I have a table called 'category'
>
> >show fields in category;
> +-------------+------------------+------+-----+---------+----------------+
> | Field | Type | Null | Key | Default | Extra |
> +-------------+------------------+------+-----+---------+----------------+
> | id | int(11) | NO | PRI | NULL | auto_increment |
> | parent | int(11) | YES | | NULL | |
> | name | char(64) | YES | | NULL | |
> | display | enum('yes','no') | YES | | NULL | |
> | description | text | YES | | NULL | |
> +-------------+------------------+------+-----+---------+----------------+
>
> This forms a tree structure under which items are listed. It is never more
> than 5 levels deep.
> The top level is defined by the parent field being zero or null.
>
> Now, I can generate a list of categories of interest easily enough by a
> sub select statement to another table
>
> e.g.
>
> >select distinct category from product where product.supplier='1' and
> not isnull(product.category);
> +----------+
> | category |
> +----------+
> | 10 |
> | 11 |
> | 12 |
> | 13 |
> | 14 |
> | 15 |
> | 16 |
> | 17 |
> | 48 |
> | 64 |
> | 66 |
> | 97 |
> | 119 |
> | 127 |
> +----------+
> 14 rows in set (0.00 sec)
>
>
>
> What I want to do, is generate a distinct list of all these *and* their
> parents and grandparents etc. up to the top level where the parent is zero
> or null.
>
> I'm stumped.
>
> I could do it in code..but I'd rather do it as a single query.

This might not count as a single query, but if you know its never more than
5 deep (which you said) then you could do it in 5 queries (one for each
level of depth) and use union to combine the results.
--
Brian Cryer
www.cryer.co.uk/brian