Ashfaque Baig Ashfaque Baig - 1 year ago 78
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

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download