June 19, 2013 at 3:53 am
Kindly help on sql query to create a view that merges data from two tables with same columns but second table (sales) has missing rows that correspond first table(Inventory)
In essense: I need those items that are in Inventory table but don’t have entries in sales table to be included in my sales view with (0) quantities.
Inventory
ItemIDItemNameQty
1Bread5
2Soda10
3Cocoa4
4Blueband15
5Omo20
6Biscuits30
7Pens50
8Note book5
Sales
ItemIDItemNameQty
1Bread3
2Soda5
5Omo10
6Biscuits15
8Note book2
I want my sales view to look like below
Sales View
ItemIDItemNameQty
1Bread3
2Soda5
3Cocoa0
4Blueband0
5Omo10
6Biscuits15
7Pens0
8Note book5
June 19, 2013 at 4:22 am
Maybe something like this:
select a.ItemID, a.ItemName, ISNULL(b.Qty, 0)
FROM Inventory AS a
LEFT JOIN Sales AS b
ON a.ItemID = b.ItemID
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
June 19, 2013 at 4:37 am
This will give you the results:
select a.ItemID, a.ItemName, a.Qty - b.Qty as current_inventory
FROM Inventory AS a
LEFT JOIN Sales AS b
ON a.ItemID = b.ItemID
When there could be more rows in Sales for each item you can use a sub-query to get the total sold quantity for each item:
select a.ItemID, a.ItemName, a.Qty - sold.Qty as current_inventory
FROM Inventory AS a
LEFT JOIN (select ItemID, SUM(Qty) as Qty FROM Sales GROUP BY ItemID) AS sold
ON a.ItemID = sold.ItemID
June 19, 2013 at 5:30 am
Thanks alot, quite helpful
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply