On 07/11/2006, at 10:03 PM, Grant Parnell - EverythingLinux wrote:
I've got a couple of tables itmmaster and suppstock. itmmaster
contains products, suppstock contains log-like entries of product
codes and stock availability and a datetime stamp. I want to do a
join to select a bunch of products along with their most recent
stock update entry and was wondering if that's possible to do in SQL.
example itmmaster data
(id)
'productA'
'productB'
'productC'
example suppstock data
(id),(stockavail),(lastupdated)
'productA','10','2006-09-01 12:00:00'
'productB','9','2006-05-01 12:00:00'
'productA','2','2006-10-01 12:00:00'
'productB','99','2006-11-01 12:00:00'
'productC','20','2006-04-28 12:00:00'
'productC','0','2006-10-01 12:00:00'
'productA','0','2006-10-10 12:00:00'
'productC','1','2006-11-05 12:00:00'
example query idea
select itmmaster.id,suppstock.stockavail,suppstock.lastupdated from
itmmaster left join suppstock on itmmaster.id,suppstock.id
where ??? group or order by something having stockavail>0;
example result set
'productB','99','2006-11-01 12:00:00'
'productC','1','2006-11-05 12:00:00'
Note that productA is missing only because of the having clause. If
I had put stockavail>0 in the where clause that'd return the last
time there was stock which is crap for me so don't suggest that.
Sure, I can do this in the client app but that's inefficient. This
crops up from time to time and I'd like to solve it properly. In
this case I'm tring to identify products in stock with our
suppliers last time we checked.
I happen to be using MySQL 4.X if that makes any difference. Maybe
I need a sub select or do a suppstock join to itself or possibly a
stored procedure if we can upgrade to MySQL 5.X?
One way would be to use a correlated subquery to select only rows
that have the latest timestamp for the given stock ID. For example:
select
itmmaster.id,
suppstock.stockavail,
suppstock.lastupdated
from
itmmaster
left join suppstock on itmmaster.id = suppstock.id
where
-- match only latest suppstock entry
suppstock.lastupdated = (select s2.lastupdated from suppstock s2
where s2.id = suppstock.id order by lastupdated desc limit 1)
-- match only entries with stock
and suppstock.stockavail>0;
You could probably also try max(s2.lastupdated) in the subquery
instead of an ordered select with limit.
Depending on constraints you could also look at redesigning the
schema so that there would be no need for a subquery. For example,
one table for current suppstock data with a unique key on product id,
and another for historical suppstock data.
--
SLUG - Sydney Linux User's Group Mailing List - http://slug.org.au/
Subscription info and FAQs: http://slug.org.au/faq/mailinglists.html
