From: Jan Steinman
Date: February 6 2010 11:28pm
Subject: Query question
List-Archive: http://lists.mysql.com/mysql/220616
Message-Id: <2CA2C91D-E4EF-42CD-9D7F-CC1687A35836@Bytesmiths.com>
MIME-Version: 1.0 (Apple Message framework v936)
Content-Type: text/plain; charset=US-ASCII; format=flowed; delsp=yes
Content-Transfer-Encoding: 7bit

I have three tables that work together.

"s_product" is a list of farm products with an autoincrementing ID.

"s_product_market_prices" is a list of market pricings, obtained from  
various sources. Each one is dated and refers to exactly one s_product  
record via its ID.

"s_product_harvest" is a list of harvests, including s_product.ID,  
amount, and date/time.

Now I want to generate a report showing the harvest sums and their  
values, based upon an appropriate market pricing. It was all happy  
when I only had one pricing per product, but then I added new dated  
pricings, and got unexpected results.

I'd be happy if the pricings used were simply in the same year as the  
harvest, but it seems like it picks a random one when I do a LEFT JOIN  
on these tables. When I put additional AND clauses on the join to get  
it to pick a price within the desired date range, it seems to affect  
the number of harvests summed, and they are reduced somehow.

(Apologies for not fully qualifying the unexpected results; I'm hoping  
someone can look at this and quickly show me something stupid I've  
done! :-)

Here's the report:
	http://www.EcoReality.org/wiki/2009_harvest

which is generated by the following SQL:
----------------
SELECT
  product AS ID,
  MAX(s_product.name) AS Name,
  SUM(quantity) AS Quantity,
  MIN(harvest.units) AS Units,
  CONCAT('$', ROUND((SUM(quantity) * prices.price), 2)) AS Value,
  prices.market_type AS `R-W`,
  COUNT(*) AS Harvests,
  MIN(date) AS Begin,
  MAX(date) AS End
FROM s_product_harvest harvest
INNER JOIN s_product on s_product.ID = harvest.product
LEFT OUTER JOIN s_product_market_prices prices ON ID = prices.product_ID
WHERE date >= '{{{1}}}-01-01' AND date <= '{{{1}}}-12-31 23:59:59'  
GROUP BY s_product.name
----------------
(Note that the token "{{{1}}}" is replaced with a four-digit year,  
like "2009".)

My first impulse was to change the LEFT OUTER JOIN to:

  s_product_market_prices prices ON ID = prices.product_ID AND  
prices.price_date >= '{{{1}}}-01-10' AND prices.price_date <=  
'{{{1}}}-12-31 23:59:59'

So that the prices table would only join for the desired year.

What am I doing wrong here?

Following are schemas of the three tables:

CREATE TABLE IF NOT EXISTS `s_product` (
`ID` int(10) unsigned NOT NULL auto_increment,
`super` int(11) default NULL COMMENT 'generalization',
`name` varchar(31) character set utf8 NOT NULL,
`units`  
enum 
('kilograms 
','grams','pounds','ounces','liters','each','cords','bales') character  
set utf8 NOT NULL default 'kilograms' COMMENT 'preferred unit',
`description` varchar(255) character set utf8 NOT NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `Name` (`name`),
KEY `Description` (`description`) )
ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='list of  
EcoReality farm products' AUTO_INCREMENT=86 ;

CREATE TABLE IF NOT EXISTS `s_product_harvest` (
`date` datetime NOT NULL COMMENT 'Date and time of harvest.',
`product` int(11) NOT NULL default '53',
`resource` varchar(255) character set utf8 NOT NULL COMMENT  
'Particular animal or tree, etc.',
`quantity` decimal(10,2) NOT NULL default '0.80',
`units`  
enum 
('kilograms 
','grams','pounds','ounces','liters','each','cords','bales') character  
set utf8 NOT NULL default 'kilograms',
`who1` smallint(5) unsigned NOT NULL default '2' COMMENT 'Who  
harvested this resource?',
`who2` smallint(5) unsigned NOT NULL default '4' COMMENT 'Who helped  
harvest this resource?',
`notes` varchar(255) character set utf8 NOT NULL,
KEY `product` (`product`),
KEY `date` (`date`),
KEY `who1` (`who1`,`who2`) )
ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin  
COMMENT='historical list of EcoReality farm products harvested';

CREATE TABLE IF NOT EXISTS `s_product_market_prices` (
`product_ID` int(11) NOT NULL,
`price_date` date NOT NULL,
`price_source` varchar(255) character set utf8 NOT NULL,
`market_type` enum('retail','wholesale') character set utf8 NOT NULL  
default 'wholesale',
`price` float NOT NULL,
`units` enum('kilograms','grams','pounds','ounces','liters','each')  
character set utf8 NOT NULL default 'kilograms' COMMENT 'change in  
sync with s_product_harvest.units',
PRIMARY KEY (`product_ID`,`price_date`) )
ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='market  
pricing information for EcoReality products';

Thanks for whatever help you can offer!
----------------
The Apocalypse has Four Horsemen: climate change, habitat destruction,  
industrial agriculture, and poverty. Each Horseman holds a whip called  
Growth in his hand. None can be stopped unless all are stopped. --  
David Foley
:::: Jan Steinman, EcoReality Co-op ::::