Ashfaque Baig Ashfaque Baig - 6 months ago 40
C# Question

Sql Table join query

I have 3 tables in sql

  1. ProductMaster with column ProductName

  2. PurchaseData with column ProductName and Quantity

  3. SaleData with column ProductName and Quantity

Please guide me to write query for table join to get the result as under

PRODUCT1 10 5 5
PRODUCT2 10 0 10
PRODUCT3 10 5 5


Try to start with something like this:

SELECT [ProductMaster].[ProductName]
    ,[PurchaseData].[Quantity]  AS [PurchasedQuantity]
    ,[SaleData].[Quantity]      AS [SoldQuantity]
        - [SaleData].[Quantity] AS [ClosingStock]
  FROM [ProductMaster]
    LEFT OUTER JOIN [PurchaseData]
        ON [ProductMaster].[ProductName] = [PurchaseData].[ProductName]
    LEFT OUTER JOIN [SaleData]
        ON [ProductMaster].[ProductName] = [SaleData].[ProductName]

The idea is to have the master table and join the two dependent tables to the master with an INNER JOIN on the ProductName field.