SQL: Bug in Joining two tables

Question

I have a item table from which i want to get Sum of item quantity

Query:

Select item_id, Sum(qty) from item_tbl group by item_id

Result:

==================
| ID  | Quantity  |
===================
| 1   | 10        |
| 2   | 20        |
| 3   | 5         |
| 4   | 20        |

The second table is invoice table from which i am getting the item quantity which is sold. I am joining these two tables as

Query:

Select item_tbl.item_id, Sum(item_tbl.qty) as [item_qty], 
-isnull(Sum(invoice.qty),0) as [invoice_qty] 
from item_tbl 
left join invoice on item_tbl.item_id = invoice invoice.item_id group by item_tbl.item_id

Result:

=================================
| ID  | item_qty  | invoice_qty |
=================================
| 1   | 10        |  -5          |
| 2   | 20        |  -20         |
| 3   | 10        |  -25         |   <------ item_qty raised from 5 to 10 ?? 
| 4   | 20        |  -20         |

I don't know if i am joining these tables in right way. Because i want to get everything from item table and available things from invoice table to maintain the inventory. So i use left join. Help please..

Modification

when i added group by item_id, qty i got this:

=================================
| ID  | item_qty  | invoice_qty |
=================================
| 1   | 10        |  -5          |
| 2   | 20        |  -20         |
| 3   | 5         |  -5          | 
| 3   | 5         |  -20         |
| 4   | 20        |  -20         |

As its a view so ID is repeated. what should i do to avoid this ??

Answer 1

Clearing things up, my answer from the comments explained:

While using left join operation (A left join B) - a record will be created for every matching B record to an A record, also - a record will be created for any A record that has no matching B record, using null values wherever needed to complement the fields from B.

I would advise reading up on Using Joins in SQL when approaching such problems.

Below are 2 possible solutions, using different assumptions.

---

Solution A

Without any assumptions regarding primary key:

We have to sum up the item quantity column to determine the total quantity, resulting in two sums that need to be performed, I would advise using a sub query for readability and simplicity.

select item_tbl.item_id, Sum(item_tbl.qty) as [item_qty], -isnull(Sum(invoice_grouped.qty),0) as [invoice_qty]
from item_tbl left join 
   (select invoice.item_id as item_id, Sum(invoice.qty) as qty from invoice group by item_id) invoice_grouped
on (invoice_grouped.item_id = item_tbl.item_id)
group by item_tbl.item_id

---

Solution B

Assuming item_id is primary key for item_tbl:

Now we know we can rely on the fact that there is only one quantity for each item_id, so we can do without the sub query by selecting any (max) of the item quantities in the join result, resulting in a quicker execution plan.

select item_tbl.item_id, Max(item_tbl.qty) as [item_qty], -isnull(Sum(invoice.qty),0) as [invoice_qty]
from item_tbl left join invoice on (invoice.item_id = item_tbl.item_id)
group by item_tbl.item_id

Answer 2

If your database design is following the common rules, item_tbl.item_id must be unique.

So just change your query:

Select item_tbl.item_id, item_tbl.qty as [item_qty], 
-isnull(Sum(invoice.qty),0) as [invoice_qty] 
from item_tbl 
left join invoice on item_tbl.item_id = invoice invoice.item_id group by item_tbl.item_id, item_tbl.qty