Mysql – How to Join Two Result sets to query on output came from Two statements

MySQL

I Have two queries
Query 1:

SELECT 
    e.eid, e.item as 'ITEM', SUM(s.qty) as 'TOTAL SOLD'
from
    tbl_sales s,
    tbl_matentry e
WHERE
    e.eid = s.item
GROUP By e.eid ;

Result 1: 

+---------+-------------+---------------+
|     eid |    ITEM     |  TOTAL SOLD   |
+---------+-------------+---------------+
|       1 | rupa        |             5 |
|       2 | pan america |             3 |
|       3 | John Player |            10 |
|       4 | classmate   |            11 |
|       5 | lepakshi    |            55 |
|       6 | lee         |            14 |
|       7 | puma        |             9 |
+---------+-------------+---------------+

Query 2:

SELECT 
    e.eid, e.item as 'ITEM', SUM(s.qty) as 'TOTAL STOCK'
from
    tbl_purchases p,
    tbl_matentry e
WHERE
     e.eid = p.item
GROUP By e.eid ;

Result 2:

+---------+-------------+--------------------+
|     eid |    ITEM     |        TOTAL STOCK |
+---------+-------------+--------------------+
|       1 | rupa        |                 41 |
|       2 | pan america |                 45 |
|       3 | John Player |                 32 |
|       4 | classmate   |                 75 |
|       5 | lepakshi    |                 56 |
|       6 | lee         |                 65 |
|       7 | puma        |                 50 |
+---------+-------------+--------------------+

When I combine both sql statements into single statement I am getting incorrect output

So Far i have tried this 

SELECT 
e.eid,
e.item as 'ITEM',
SUM(p.qty) as 'TOTAL STOCK',
SUM(s.qty) as ' TOATL SOLD',
(SUM(p.qty)-SUM(s.qty)) as 'BALANCE STOCK'
FROM
tbl_matentry e
    LEFT OUTER JOIN
tbl_purchases p ON p.item = e.eid
    LEFT OUTER JOIN
tbl_sales s ON s.item=e.eid
GROUP BY e.eid ORDER BY e.eid

+-----+-------------+-------+------+---------+
| eid |    ITEM     | STOCK | SOLD | BALANCE |
+-----+-------------+-------+------+---------+
|   1 | rupa        |   123 |   15 |     108 |
|   2 | pan america |    90 |    6 |      84 |
|   3 | John Player |    64 |   20 |      44 |
|   4 | classmate   |   150 |   22 |     128 |
|   5 | lepakshi    |   112 |  110 |       2 |
|   6 | lee         |   130 |   28 |     102 |
|   7 | puma        |   100 |   18 |      82 |
+-----+-------------+-------+------+---------+

How to do i get correct result? Where is mistake/error in my query.Please review my Query and help me to get correct data.

My Expected Output is 

+---------+-------------+------------------+-----------------+------------------+
|     eid |    ITEM     |      TOTAL STOCK |   TOTAL SOLD    |  BALANCE STOCK   |
+---------+-------------+------------------+-----------------+------------------+
|       1 | rupa        |               41 |               5 |               36 |
|       2 | pan america |               45 |               3 |               42 |
|       3 | John Player |               32 |              10 |               22 |
|       4 | classmate   |               75 |              11 |               64 |
|       5 | lepakshi    |               56 |              55 |                1 |
|       6 | lee         |               65 |              14 |               51 |
|       7 | puma        |               50 |               9 |               41 |
+---------+-------------+------------------+-----------------+------------------+

Best Answer

The problem that you are having is most likely coming from the tbl_sales because your item can appear in the table more than once. When you then JOIN your tables together, you are returning the qty from tbl_purchases for each row in tbl_sales.

You can see this behavior by performing a SELECT on your tables:

SELECT e.*, p.qty purQty, s.qty salesQty
FROM tbl_matentry e
LEFT OUTER JOIN tbl_purchases p 
 ON p.item = e.eid
LEFT OUTER JOIN tbl_sales s 
  ON s.item=e.eid
ORDER BY e.eid;

See Demo. You will see in my sample, that the rupa qty appears twice because there are two entries in tbl_sales.

One way to get the result would be to calculate the TotalSold and TotalStock in subqueries:

select e.eid, 
  e.item,
  coalesce(p.TotalStock, 0) TotalStock,
  coalesce(s.TotalSold, 0) TotalSold,
  coalesce(p.TotalStock, 0) - coalesce(s.TotalSold, 0) BalanceStock
from tbl_matentry e
left join
(
  select item, sum(qty) TotalSold
  from tbl_sales
  group by item
) s
  on e.eid = s.item
left join
(
  select item, sum(qty) TotalStock
  from tbl_purchases
  group by item
) p
  on e.eid = p.item;

See SQL Fiddle with Demo.

This could also be written using a single subquery to tbl_sales:

select e.eid, 
  e.item,
  sum(p.qty) TotalStock,
  coalesce(s.TotalSold, 0) TotalSold,
  coalesce(sum(p.qty)) - coalesce(s.TotalSold, 0) BalanceStock
from tbl_matentry e
left join tbl_purchases p
  on e.eid = p.item
left join
(
  select item, sum(qty) TotalSold
  from tbl_sales
  group by item
) s
  on e.eid = s.item
group by e.eid, e.item, TotalSold;

See SQL Fiddle with Demo

Related Question