CODEWITHSUNDEEP
sqlsql-serverjoin
parkourkarthik
parkourkarthik

Reputation: 834

SQL Multiple JOIN Query Not successful

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 JOINs 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

Answers (2)

Peter G
Peter G

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

thevan
thevan

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

Related Questions