title: Getting stock balance
body: <p>I have 3 tables as follows:</p>
  
  <pre><code>products:[product_id,product_name]
  stock:[product_id,date,qty_received]
  sales:[product_id,date,qty_sold]
  </code></pre>
  
  <p>I'm trying to display the balance of all products by subtracting qty_received - qty_sold.
  My query is: </p>
  
  <pre><code>SELECT DISTINCT p.product_id, p.product_name,
  IFNULL(sum(qty_received),0)-IFNULL(sum(qty_sold),0) as balance FROM products p
  LEFT JOIN stock st ON (p.product_id=st.product_id)
  LEFT JOIN sales sa ON (p.product_id=sa.product_id)
  GROUP BY p.product_id
  </code></pre>
  
  <p>With the above query I'm not getting the correct balance.
  Could you please help.</p>
  
  <p>Additional Information:
  Following are a simplified structure for my tables
  Products table:</p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS `products` (
    `product_id` varchar(15) NOT NULL,
    `product_name` varchar(20) NOT NULL,
    UNIQUE KEY `product_id` (`product_id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  </code></pre>
  
  <p>-- Dumping data for table <code>products</code></p>
  
  <pre><code>INSERT INTO `products` (`product_id`, `product_name`) VALUES
    ('111', 'Product One'),
    ('222', 'Product Two'),
    ('333', 'Product Three'),
    ('444', 'Product Four');
  </code></pre>
  
  <p>Stock table:</p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS `stock` (
    `stock_id` int(11) NOT NULL AUTO_INCREMENT,
    `product_id` int(11) NOT NULL,
    `qty_received` int(11) NOT NULL,
    PRIMARY KEY (`stock_id`)
  ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=112 ;
  </code></pre>
  
  <p>-- Dumping data for table <code>stock</code></p>
  
  <pre><code>INSERT INTO `stock` (`stock_id`, `product_id`, `qty_received`) VALUES
    (1, 111, 5),
    (2, 222, 10),
    (3, 333, 4),
    (4, 444, 6),
    (5, 111, 2),
    (6, 222, 7),
    (7, 111, 3),
    (8, 111, 3);
  </code></pre>
  
  <p>Sales Table</p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS `sales` (
    `sales_id` int(11) NOT NULL AUTO_INCREMENT,
    `product_id` int(11) NOT NULL,
    `qty_sold` int(11) NOT NULL,
    PRIMARY KEY (`sales_id`)
  ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;
  </code></pre>
  
  <p>-- Dumping data for table <code>sales</code></p>
  
  <pre><code>INSERT INTO `sales` (`sales_id`, `product_id`, `qty_sold`) VALUES
    (1, 111, -1),
    (2, 222, -3),
    (3, 333, -2),
    (4, 111, -3),
    (5, 222, -1),
    (6, 222, -4);
  </code></pre>
  
  <p>The Result I'm getting is:</p>
  
  <pre><code>+------------+--------------+---------+
  | product_id | product_name | balance |
  +------------+--------------+---------+
  | 111        | Product One  | 10      |
  | 222        | Product Two  | 35      |
  | 333        | Product Three| 2       |
  | 444        | Product Four | 6       |
  +------------+--------------+---------+
  </code></pre>
  
  <p>Correct balance I'm expecting is</p>
  
  <pre><code>+------------+--------------+---------+
  | product_id | product_name | balance |
  +------------+--------------+---------+
  | 111        | Product One  | 9       |
  | 222        | Product Two  | 9       |
  | 333        | Product Three| 2       |
  | 444        | Product Four | 6       |
  +------------+--------------+---------+
  </code></pre>
  
  <p>Finally got a working sql for this solution , but not sure if this is the best way doing this.  I'm very concerned about the performance, since these tables got large amount of records.</p>
  
  <pre><code>SELECT p.product_id,p.product_name,
  IFNULL(sum(st.qty_received),0)+IFNULL(sold,0) as balance FROM products p
    LEFT JOIN stock st ON (p.product_id=st.product_id)
    LEFT JOIN (SELECT sa.product_id,sum(sa.qty_sold) as sold FROM sales sa 
       GROUP BY sa.product_id) as t1 ON (t1.product_id=p.product_id)
  GROUP BY product_id
  </code></pre>
  
  <p>Anyone can suggest a better sql (or change in schema) for better speed/performance to get stock balance?</p>