From: Peter Brawley
Date: August 24 2010 5:43pm
Subject: Re: Complex Select Query
List-Archive: http://lists.mysql.com/mysql/222655
Message-Id: <10989307.1282671814291.JavaMail.root@elwamui-darkeyed.atl.sa.earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit

>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.

PB

-----

-----Original Message-----
>From: Victor Subervi <victorsubervi@stripped>
>Sent: Aug 24, 2010 1:14 PM
>To: mysql@stripped
>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