How to get item details from two tables (purchase and sales)

Question

1.00/5 (2 votes)

See more:

I need a report to get the item details i.e the purchase and sales done for that item

What I have tried:

Select (A.ItemCode), A.ItemName, A.Barcode, A.Unit, A.PurchaseRate, A.SellingRate, A.DiscountPercent, A.OpeningStock, A.BatchNo, A.ExpiryDate AS ExpDate,
				B.Quantity AS StockQuantity,E.PurchaseDate,F.InvoiceDate from tblItemMaster A
				INNER JOIN tblStockMaster B ON B.ItemCode=A.ItemCode AND B.BatchNo=A.BatchNo
				INNER JOIN tblPurchaseDetails C ON C.ItemCode=A.ItemCode AND C.BatchNo=A.BatchNo
				INNER JOIN tblSalesDetails D ON D.ItemCode=A.ItemCode AND D.BatchNo=A.BatchNo
				INNER JOIN tblPurchaseMaster E ON E.PurchaseBill=C.PurchaseBill
				INNER JOIN tblSalesMaster F ON F.InvoiceNo=D.InvoiceNo
				Where A.ItemCode='10'

Posted 28-Jun-18 0:34am

Member 13569764

Updated 30-Jun-18 1:48am

Add a Solution

Comments

#realJSOP 28-Jun-18 6:46am

Identifying the problem is 50% of coming up with a solution. What's your question?

Member 13569764 28-Jun-18 6:48am

i have sales table purchase table i need to get all the details from these two tables according to the item

#realJSOP 28-Jun-18 7:24am

That's nice, but you have SIX tables referenced in your query, and you're retrieving data from four of them. Which TWO are you referring to?

Member 13569764 28-Jun-18 7:28am

I have Sales as Master and Details 2 tables same for purchase
I need to get the query like this
Sno. ItemCode Type Date Quantity Rate Total
1 10 Sales 1/12/2018 5 20 25
2 10 Purchase 11/11/2018 50 18 500
3 ......

#realJSOP 28-Jun-18 8:22am

Edit your question so someone can answer it.

W∴ Balboos, GHB 28-Jun-18 8:45am

Let me rephrase for you what is in the past several responses:

You show us what you tried. Well? What happened?

As you wrote it, for all we know, what you tried worked perfectly.

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

Gerry Schmitz · Answer 1 · 2018-06-28T09:23:00

Solution 1

Your "INNER JOINS" will yield nothing if ANY table does not contain a corresponding record (i.e. you're assuming every item has "sales" AND "purchases").

You need to consider "OUTER JOINS".

You should probably also do a "UNION" on your sales and purchase details as "transactions".

Posted 28-Jun-18 9:23am

Gerry Schmitz

Updated 28-Jun-18 9:28am

v2

Comments

kmoorevs 30-Jun-18 17:55pm

Just to add, subqueries in the following form can solve many problems:
Select Sum(sq.Sum_Sales) as Sales, Sum(sq.Sum_Purchases) as Purchases, sq.ItemCode
From (Select Sum(A.Sales) as Sum_Sales, 0 as Sum_Purchases, A.ItemCode
From tblSales A
Group By A.ItemCode
Union All
Select 0 as Sum_Sales, Sum(B.Purchases) as Sum_Purchases, B.ItemCode
From tblPurchases B
Group By B.ItemCode) as sq

Mohibur Rashid · Answer 2 · 2018-06-28T14:05:00

This is your query:

SQL

SELECT
    A.ItemCode
  , A.ItemName
  , A.Barcode
  , A.Unit
  , A.PurchaseRate
  , A.SellingRate
  , A.DiscountPercent
  , A.OpeningStock
  , A.BatchNo
  , A.ExpiryDate AS ExpDate
  , B.Quantity AS StockQuantity
  , E.PurchaseDate
  , F.InvoiceDate 
FROM
  tblItemMaster                 A
  INNER JOIN tblStockMaster     B ON B.ItemCode       = A.ItemCode AND B.BatchNo=A.BatchNo
  INNER JOIN tblPurchaseDetails C ON C.ItemCode       = A.ItemCode AND C.BatchNo=A.BatchNo
  INNER JOIN tblPurchaseMaster  E ON E.PurchaseBill   = C.PurchaseBill
  INNER JOIN tblSalesDetails    D ON D.ItemCode       = A.ItemCode AND D.BatchNo=A.BatchNo
  INNER JOIN tblSalesMaster     F ON F.InvoiceNo      = D.InvoiceNo
WHERE
  A.ItemCode='10'

Query seems alright. The query will result fine as long as you have a sails record for that item. If you want to see results even if no sales exists. Then rewrite as follow:

SQL

-- INNER JOIN reordered
SELECT
    A.ItemCode
  , A.ItemName
  , A.Barcode
  , A.Unit
  , A.PurchaseRate
  , A.SellingRate
  , A.DiscountPercent
  , A.OpeningStock
  , A.BatchNo
  , A.ExpiryDate AS ExpDate
  , B.Quantity AS StockQuantity
  , E.PurchaseDate
  , F.InvoiceDate 
FROM
  tblItemMaster A
  INNER JOIN tblStockMaster     B ON B.ItemCode       = A.ItemCode AND B.BatchNo=A.BatchNo
  INNER JOIN tblPurchaseDetails C ON C.ItemCode       = A.ItemCode AND C.BatchNo=A.BatchNo
  INNER JOIN tblPurchaseMaster  E ON E.PurchaseBill   = C.PurchaseBill
  LEFT OUTER JOIN tblSalesDetails    D ON D.ItemCode       = A.ItemCode AND D.BatchNo=A.BatchNo
  LEFT OUTER JOIN tblSalesMaster     F ON F.InvoiceNo      = D.InvoiceNo
WHERE
  A.ItemCode='10'

There is one issue though. What would happen if item was returned and resold? Since I do not know how you manage this. I can't suggest anything.

muhammad sufiyan · Answer 3 · 2018-06-30T01:50:00

SELECT (what items you want to achieve)
FROM (select the table from which you want to achieve any data from, if tables are different then use innerjoin with alias)(for example: FROM Table_a as a INNERJOINS Table_b as b on a.empID=b.empID)(innerjoin will happen until the data is achieved and will only happen if the attributes are same or linked with one another being in different tables)
WHERE (whatever the condition is) (For example: WHERE b.empID="5555")

How to get item details from two tables (purchase and sales)

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0