On Tue, 7 Nov 2006, Sam Lawrance wrote:
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.
I was just getting excited about your above suggestion but realised the
sub-query's just going to select literally the most recent log entry where
I really want the most recent log entries for all products. It'd work if I
could add to the subquery "where [EMAIL PROTECTED]" where @variable gets set
by the main select. This depends on the internal order of processing and
I'll check that out.
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.
Yeah I had thought of that... perhaps I will, there's only 202,827
products at the moment.
--
---<GRiP>---
Grant Parnell - senior LPIC-1 certified consultant
Linux User #281066 at http://counter.li.org (Linux Counter)
EverythingLinux services - the consultant's backup & tech support.
Web: http://www.everythinglinux.com.au/support.php
We're also busybits.com.au and linuxhelp.com.au and elx.com.au.
Phone 02 8756 3522 to book service or discuss your needs.
ELX or its employees participate in the following:-
OSIA (Open Source Industry Australia) - http://www.osia.net.au
AUUG (Australian Unix Users Group) - http://www.auug.org.au
SLUG (Sydney Linux Users Group) - http://www.slug.org.au
LA (Linux Australia) - http://www.linux.org.au
--
SLUG - Sydney Linux User's Group Mailing List - http://slug.org.au/
Subscription info and FAQs: http://slug.org.au/faq/mailinglists.html
