I'll take this on board and give it a go.
FYI table design is way out of my control - I'm just building a report on someone elses data (you are right, it is ugly).
It's nearly mid-night here now so I might get on to it tomorrow.
Thank you heaps for you help ... Ross
At 11:10 PM 3/08/2004, you wrote:
With MySQL 4.1: try this (untested)
SELECT SM.key, SM.stock_on_hand COALESCE( SH.tally, 0 ) AS hist_sales FROM stock_master AS SM LEFT JOIN ( SELECT SH.key SUM( SH.jan ) + ... + SUM( SH.`dec` ) AS tally FROM stock_history AS SH WHERE SH.year = 2004 GROUP BY key ) AS H ON H.key = SM.key HAVING stock_on_hand > hist_sale
With earlier versions, you must create a temporary table with the table appearing in the subquery.
Notes: 2 ugly things about your design: - dashes in table and column names - one column per month: you must have one column "month" that becomes part of the primary key
HTH Ignatius _________________________ ----- Original Message ----- From: "Ross Honniball" <ross@xxxxxxxxxxxxxxxxxxx> To: "php DB" <php-db@xxxxxxxxxxxxx> Sent: Tuesday, August 03, 2004 2:54 PM Subject: Re: Web page paginator that doesn't rely on the LIMIT clause
> At 10:22 PM 3/08/2004, you wrote: > >1. please answer to the list, not to only me (you break the thread) > > whoops. sorry. > > >2. show us your data model if you need further help > > AS AN EXERCISE, I would be interested to know if you can do this in one > query, but I've pretty much decided to either code up or find a paginator > class that will resolve my present issues. I figure even if this particular > query can be done in SQL, I'm going to eventually run against a problem > that can't. > > Roughly, data model looks like this: > > Table : Stock-Master > Key (key) > title etc. > stock-on-hand > | > | > /\ > / \ > Table : Stock-History > Key (key) > Year(key) > Sales-Jan > Sales-Feb > Sales-Mar > ... > Sales-Dec > > And what I need to do is loop through master records, grab history records > for various years (which may or may not exist for any given key), add up > multiple sales field within each history record, and compare this against > stock-on-hand to decide if I want to 'output' the record or not. > > Let me know if you want more detail. It would resolve the present issue if > it can be done in a query(and improve my sql knowledge). > > >_________________________ > >----- Original Message ----- > >From: "Ross Honniball" <ross@xxxxxxxxxxxxxxxxxxx> > >To: "Ignatius Reilly" <ignatius.reilly@xxxxxxx> > >Sent: Tuesday, August 03, 2004 1:46 PM > >Subject: Re: Web page paginator that doesn't rely on the LIMIT > >clause > > > > > > > thanks ignatius. not sure i'm out of the woods though. > > > > > > I didn't know sql had CASE and IF. I just had a quick look in the manual > > > and aren't sure they will help. > > > > > > The specific logic I'm doing is this: > > > > > > total_sales = 0; > > > select * from master > > > while more master records > > > if (select * from history record 1) // MAY NOT EVEN EXIST > > > loop through a dozen monthly sales fields adding into total_sales; > > > endif > > > if (select * from history record 2) // MAY NOT EVEN EXIST > > > loop through a dozen monthly sales fields adding into total_sales; > > > endif > > > //********* the 'output' test > > > if total_sales > some_amount > > > output record > > > endif > > > end-while > > > > > > So it's not real complex to do in code, but I really wouldn't know where > >to > > > start to try and screw it all in to a single sql statement. > > > > > > I also had a look at HTML_Pager but, at a glance, it looks like some kind > > > of web-page presenter? > > > > > > At 09:14 PM 3/08/2004, you wrote: > > > >1. What prevents you from implementing the conditions directly in SQL? > >You > > > >can achieve a lot with CASE and IF. > > > >2. For your paging needs, you may benefit from investigating the PEAR > > > >HTML_Pager class. > > > > > > > >Ignatius > > > >_________________________ > > > >----- Original Message ----- > > > >From: "Ross Honniball" <ross@xxxxxxxxxxxxxxxxxxx> > > > >To: "php DB" <php-db@xxxxxxxxxxxxx> > > > >Sent: Tuesday, August 03, 2004 12:52 PM > > > >Subject: Web page paginator that doesn't rely on the LIMIT > >clause > > > > > > > > > > > > > Hi all, > > > > > > > > > > I use the LIMIT statement to control paging of data, which works > >really > > > >well. > > > > > > > > > > I now have a situation where I need to use some logic in addition to > >the > > > > > query that will result in NOT ALL records being spat out. ie. > > > > > > > > > > select * from x where y; > > > > > foreach result > > > > > if (some condition) > > > > > output; > > > > > endif > > > > > endfor > > > > > > > > > > So problem is I can't say LIMIT 20,20 on the query as logic may result > >in > > > > > less than 20 records being spat out to screen. > > > > > > > > > > I was planning on coding up a solution so that i just keep a count > >myself > > > > > of how many records I have output myself and break at appropriate > >paging > > > > > points, but I am probably re-inventing the wheel. > > > > > > > > > > Anyone know of any good classes they are using that do this sort of > >thing > > > > > already? > > > > > . > > > > > . Ross Honniball. JCU Bookshop Cairns, Qld, Australia. > > > > > . > > > > > > > > > > -- > > > > > PHP Database Mailing List (http://www.php.net/) > > > > > To unsubscribe, visit: http://www.php.net/unsub.php > > > > > > > > > > > > > > > > > >-- > > > >PHP Database Mailing List (http://www.php.net/) > > > >To unsubscribe, visit: http://www.php.net/unsub.php > > > > > > > > > . Ross Honniball JCU Bookshop Cairns Supervisor > > > . James Cook Uni, McGregor Rd, Smithfield, Qld. 4878, Australia > > > . Ph:07.4042.1157 Fx:07.4042.1158 Em:ross@xxxxxxxxxxxxxxxxxxx > > > . There are no problems. Only solutions. > > > > > > > > . > . Ross Honniball. JCU Bookshop Cairns, Qld, Australia. > . > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > >
. . Ross Honniball. JCU Bookshop Cairns, Qld, Australia. .
-- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
