Alternatively, you could just code a recursive function to do the same instead of doing it in SQL or stored procs.
On Tue, Aug 24, 2010 at 11:01 AM, Victor Subervi <victorsube...@gmail.com>wrote: > On Tue, Aug 24, 2010 at 1:43 PM, Peter Brawley > <peter.braw...@earthlink.net>wrote: > > > >What I'm trying to accomplish is to order the results such that after > > >stacking the data for all results for a certain category, that the next > > >results to be stacked should be those whose parent = the former > category, > > >then move on to the next category, etc. How do I do this? > > > > It's a tree. See > > http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html and > > http://www.artfulsoftware.com/infotree/treequeryperformance.pdf. > > > > > How do I cut down the tree? That stuff is *way* too complex for my needs. I > just thought I'd make my presentation of data a little cleaner but frankly > it ain't worth going through all that learning and experimentation to do > it. > If there isn't another way I'll just forget about it. Any other suggestions > would be nice. > TIA. > Victor > > > ----- > > > > -----Original Message----- > > >From: Victor Subervi <victorsube...@gmail.com> > > >Sent: Aug 24, 2010 1:14 PM > > >To: mysql@lists.mysql.com > > >Subject: Complex Select Query > > > > > >Hi; > > >I have the following query: > > > > > >select * from spreadsheets s join products p on p.Item=s.Item join > > >categories c on p.Category=c.ID where s.Client=%s order by p.category, > > >c.parent; > > > > > >mysql> describe products; > > >+-------------+--------------+------+-----+---------+----------------+ > > >| Field | Type | Null | Key | Default | Extra | > > >+-------------+--------------+------+-----+---------+----------------+ > > >| ID | int(4) | NO | PRI | NULL | auto_increment | > > >| Category | int(3) | YES | | NULL | | > > >| Item | varchar(20) | YES | UNI | NULL | | > > >| Description | varchar(255) | YES | | NULL | | > > >| UOM | varchar(20) | YES | | NULL | | > > >| Cost | float(7,2) | YES | | NULL | | > > >+-------------+--------------+------+-----+---------+----------------+ > > >6 rows in set (0.00 sec) > > > > > >mysql> describe categories; > > >+----------+-------------+------+-----+---------+----------------+ > > >| Field | Type | Null | Key | Default | Extra | > > >+----------+-------------+------+-----+---------+----------------+ > > >| ID | int(3) | NO | PRI | NULL | auto_increment | > > >| Category | varchar(20) | YES | UNI | NULL | | > > >| Parent | varchar(20) | YES | | NULL | | > > >+----------+-------------+------+-----+---------+----------------+ > > > > > >What I'm trying to accomplish is to order the results such that after > > >stacking the data for all results for a certain category, that the next > > >results to be stacked should be those whose parent = the former > category, > > >then move on to the next category, etc. How do I do this? > > >TIA, > > >Victor > > > > >
