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
>
>
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
