List:General Discussion« Previous MessageNext Message »
From:Ashish Mukherjee Date:August 25 2010 7:30am
Subject:Re: Complex Select Query
View as plain text  
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 <victorsubervi@stripped>wrote:

> On Tue, Aug 24, 2010 at 1:43 PM, Peter Brawley
> <peter.brawley@stripped>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 <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
> >
> >
>

Thread
Complex Select QueryVictor Subervi24 Aug
Re: Complex Select QueryPeter Brawley24 Aug
  • Re: Complex Select QueryVictor Subervi24 Aug
    • Re: Complex Select QueryAshish Mukherjee25 Aug