Click here to Skip to main content
14,668,708 members
Rate this:
Please Sign up or sign in to vote.
See more:
I want to connect 3 tables for data.

1)Products table (tbl_Prod)
Pid,Pname

2)Purchase Table(tbl_purc)
Pid,pqty,Purc_date

Sold Table (tbl_sold)
Pid,Sqty,sold_date

Result Required
1st date to 2nd date

Pid,pname,pqty-sqty = bal
101,soap, 100-20 = 80


What I have tried:

how to join 3 tables in sql and display the result
Posted
Updated 9-Aug-20 3:26am

Rate this:
Please Sign up or sign in to vote.

Solution 1

You will probably need to improve those tables by adding an ID column to two of them: SQL will not allow you to have two identical rows and it's really easy to sell to of the same product on the same day to different customers for example.

But joining them is simple:
SELECT p.PID, p.PName, (pu.pqty - s.sqty) as Balance
FROM tbl_prod p
JOIN tbl_purc pu
   ON p.PID = pu.PID
JOIN tbl_sold s
   ON p.PID = s.PID AND s.sold_date = pu.purc_date
   
Rate this:
Please Sign up or sign in to vote.

Solution 2

Whatever OG has shared above was probably what you were looking for. Would suggest you to learn about JOINS and try yourself.

Refer:
Joins (SQL Server) - SQL Server | Microsoft Docs[^]
Types of Table Relationships | Microsoft Docs[^]

Quote:
A join condition defines the way two tables are related in a query by:

- Specifying the column from each table to be used for the join. A typical join condition specifies a foreign key from one table and its associated key in the other table.
- Specifying a logical operator (for example, = or <>,) to be used in comparing values from the columns.

An Example (different from what you asked):
SELECT ProductID, Purchasing.Vendor.BusinessEntityID, Name
FROM Purchasing.ProductVendor JOIN Purchasing.Vendor
    ON (Purchasing.ProductVendor.BusinessEntityID = Purchasing.Vendor.BusinessEntityID)
WHERE StandardPrice > $10
  AND Name LIKE N'F%'

The select returns the product and supplier information for any combination of parts supplied by a company for which the company name starts with the letter F and the price of the product is more than $10.
   
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100