Complex Select Query

am 24.08.2010 19:14:54 von Victor Subervi

Re: Complex Select Query

am 24.08.2010 19:43:34 von Peter Brawley

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

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Complex Select Query

am 24.08.2010 20:01:59 von Victor Subervi

--0016e6d64717bfbb5f048e958fff
Content-Type: text/plain; charset=ISO-8859-1

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

--0016e6d64717bfbb5f048e958fff--

Re: Complex Select Query

am 25.08.2010 09:30:39 von ASHISH MUKHERJEE

--00032557563ac12962048ea0db19
Content-Type: text/plain; charset=ISO-8859-1

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 wrote:

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

--00032557563ac12962048ea0db19--