How to join 3 tables in SQL and display the result

Question

1.00/5 (1 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 8-Aug-20 23:40pm

Member239258

Updated 9-Aug-20 3:26am

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

OriginalGriff · Answer 1 · 2020-08-09T01:13:00

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:

SQL

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

Sandeep Mewara · Answer 2 · 2020-08-09T03:26:00

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):

SQL

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.

How to join 3 tables in SQL and display the result

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0