Reputation: 834
Background
I have three tables: Stock
, PurchaseEntry
, and SalesEntry
.
These tables have the fields ProductName
, MRP
, LandinPrice
and Qty
in common.
I need to join these tables in order to get SUM(Qty)
in PurchaseEntry
and SalesEntry
with the same ProductName
, MRP
, and LandinPrice
as Stock
.
Problem
I tried using the following query, but the result was not as I desired.
SUM(Qty)
of PurchaseEntry
Gives some random results, whereas SUM(Qty)
of SalesEntry
is correct.
But when I use JOIN
on PurchaseEntry
alone individually, I get the correct values.
Question
How I can get the proper results, and what type of JOIN
s should be used?
SQL Code
SELECT Stock.ProductName,
Stock.MRP,
Stock.LandinPrice,
SUM(PurchaseEntry.TotalQty)
AS PurchaseQty,
SUM(SalesEntry.Qty)
AS SalesQty
FROM Stock
JOIN PurchaseEntry
ON Stock.ProductName = PurchaseEntry.ProductName
AND Stock.MRP = PurchaseEntry.MRP
AND Stock.LandinPrice = PurchaseEntry.LandinPrice
JOIN SalesEntry
ON Stock.ProductName = SalesEntry.ProductName
AND Stock.MRP = SalesEntry.MRPPrice
AND Stock.LandinPrice = SalesEntry.LandingAmt
GROUP BY Stock.ProductName,
Stock.MRP,
Stock.LandinPrice
Solved:
I tried changing the Query myself. And I got the proper Output that i required. Below is that Query.
SELECT Stock.ProductName,
Stock.MRP,Stock.LandinPrice,
(SELECT SUM(PurchaseEntry.TotalQty)
FROM PurchaseEntry
WHERE Stock.ProductName=PurchaseEntry.ProductName
AND Stock.MRP=PurchaseEntry.MRP
AND Stock.LandinPrice=PurchaseEntry.LandinPrice) AS PurchaseQty,
(SELECT SUM(SalesEntry.Qty)
FROM SalesEntry
WHERE SalesEntry.ProductName=Stock.ProductName
AND SalesEntry.MRPPrice=Stock.MRP
AND SalesEntry.LandingAmt= Stock.LandinPrice ) AS SalesQty
FROM Stock
GROUP BY Stock.ProductName,Stock.MRP,Stock.LandinPrice
Upvotes: 2
Views: 125
Reputation: 1623
You might try something like
SELECT Stock.ProductName,Stock.MRP,Stock.LandinPrice,
(SELECT SUM(PurchaseEntry.TotalQty)
FROM PurchaseEntry
WHERE PurchaseEntry.ProductName=Stock.ProductName
AND PurchaseEntry.MRP=Stock.MRP
AND PurchaseEntry.LandinPrice=Stock.LandinPrice) AS PurchaseQty
(SELECT SUM(SalesEntry.Qty)
FROM SalesEntry
WHERE SalesEntry.ProductName=Stock.ProductName
AND SalesEntry.MRPPrice=Stock.MRP
AND SalesEntry.LandingAmt=Stock.LandinPrice) AS SalesQty
FROM Stock
Your existing query would have PurchaseQty multiplied by the number of sales and SalesQty multiplied by the number of purchases. Of course I'm assuming that the combination of stock ProductName, MRP, and LandinPrice are unique.
Upvotes: 1
Reputation: 10364
SELECT
Stock.ProductName,
Stock.MRP,
Stock.LandinPrice,
SUM(PurchaseEntry.TotalQty) AS PurchaseQty,
Sales.SalesQty
FROM
Stock
INNER JOIN PurchaseEntry ON Stock.ProductName = PurchaseEntry.ProductName AND Stock.MRP = PurchaseEntry.MRP
AND Stock.LandinPrice = PurchaseEntry.LandinPrice
INNER JOIN
(
SELECT SUM(SE.Qty) AS 'SalesQty', SE.ProductName, SE.LandingAmt, SE.MRPPrice
FROM SalesEntry SE INNER JOIN
Stock S ON S.LandinPrice = SE.LandingAmt AND S.MRP = SE.MRPPrice AND S.ProductName = SE.ProductName
GROUP BY SE.ProductName, SE.LandingAmt, SE.MRPPrice
) Sales ON Sales.ProductName = Stock.ProductName AND Sales.LandingAmt = Stock.LandinPrice
AND Sales.MRPPrice = Stock.MRP
GROUP BY
Stock.ProductName,
Stock.MRP,
Stock.LandinPrice
Upvotes: 0