Multi-way join. Are results correct?

[SteveO]

The following query is using an OSCommerce database and I need to be absolutely sure that the results are correct as I'm entering them on my tax return!

Heres the definitions for the tables concerned (I've stripped out fields which aren't referenced in this query):

CREATE TABLE `orders` (
  `orders_id` int(11) NOT NULL auto_increment,
  `date_purchased` datetime default NULL,
  `orders_status` int(5) NOT NULL default '0',
  PRIMARY KEY  (`orders_id`)
) TYPE=MyISAM AUTO_INCREMENT=441 ;

CREATE TABLE `orders_products` (
  `orders_products_id` int(11) NOT NULL auto_increment,
  `orders_id` int(11) NOT NULL default '0',
  `products_id` int(11) NOT NULL default '0',
  `products_model` varchar(12) default NULL,
  `products_name` varchar(64) NOT NULL default '',
  `products_price` decimal(15,4) NOT NULL default '0.0000',
  `final_price` decimal(15,4) NOT NULL default '0.0000',
  `products_tax` decimal(7,4) NOT NULL default '0.0000',
  `products_quantity` int(2) NOT NULL default '0',
  PRIMARY KEY  (`orders_products_id`)
) TYPE=MyISAM AUTO_INCREMENT=840 ;

CREATE TABLE `products` (
  `products_id` int(11) NOT NULL auto_increment,
  `products_quantity` int(4) NOT NULL default '0',
  `products_model` varchar(12) default NULL,
  `products_price` decimal(15,4) NOT NULL default '0.0000',
  `products_cost_price` decimal(15,2) default NULL,
  PRIMARY KEY  (`products_id`),
  KEY `idx_products_date_added` (`products_date_added`)
) TYPE=MyISAM AUTO_INCREMENT=390 ;

SELECT count( op.products_id ) AS amount, op.products_name, op.products_model, p.products_model, p.products_cost_price*count(op.products_id) as total, p.products_cost_price
  FROM orders o, orders_products op, products p 
    WHERE op.products_id=p.products_id AND op.orders_id = o.orders_id 
    AND o.date_purchased <= '2004-01-26' 
    AND o.orders_status =3 
      GROUP BY op.products_name, p.products_cost_price 
        ORDER BY o.date_purchased

Basically what this is meant to do is get a list of all orders before a certain date and with a status of delivered (= 3). With this list of orders get a list of all products which make up these orders. Group each product together and count how many of each product sold. With this count multiply it by the cost price of the product to get the total cost price.

NB. The extra columns op.products_model and p.products_model are there as there was an error in my DB and some of the non-normalised data was not correct across tables and I wanted to check this fact. It doesn't have anything to do with the results I am after but I left it in for the moment so long as its not affecting results.

This query does work but I want to know if the numbers it is giving me are the numbers I am expecting. Thanks for any info you can give - even if its to say its ok!

[swampBoogie]

The query is not semantically correct. It is a disgrace that Mysql allows it at all.

SELECT count(*) AS amount, 
   op.products_name, 
   op.products_model, 
   p.products_model, 
   o.date_purchased,
   sum(p.products_cost_price) as total
  FROM orders o, orders_products op, products p 
    WHERE op.products_id=p.products_id AND op.orders_id = o.orders_id 
    AND o.date_purchased <= '2004-01-26' 
    AND o.orders_status =3 
      GROUP BY op.products_name, op.products_model, p.products_model, o.date_purchased
        ORDER BY o.date_purchased

When doing a group by, all columns in the select list should either be the argument of an aggregate function or present in the group by clause. Also, the columns used in the order by clause must also be present in the select-clause (i.e. when doing group by, not otherwise).

[SteveO]

Hey swampBoogie,

Thanks for that - its amazing what mySQL will let fly!

The only problem I have now is that count(*) is not counting the number of rows and is reporting just 1 row. I tried putting count(op.products_id) back and that has the same effect.

Any ideas why the count is not working as before?