SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Guru SteveO's Avatar
    Join Date
    Jan 2003
    Posts
    625
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Multi-way join. Are results correct?

    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):

    Code:
    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 ;
    Code:
    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!

  2. #2
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    697
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    The query is not semantically correct. It is a disgrace that Mysql allows it at all.

    Code:
    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).

  3. #3
    SitePoint Guru SteveO's Avatar
    Join Date
    Jan 2003
    Posts
    625
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •