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

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 |

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

StackExchange Database Administrators Q#47861, answer score: 14