Click here to Skip to main content
15,116,654 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Dear All,

We have 2 tables where the total items details store in Table1.In Table 2 the received items details storing.

Eg:-

Table 1

ID	ITEM	QTY
1	I1	2
2	I1	2
3	I2	10


Table2

ID   ITEM	QTY
1	I1	10
2	I2	10

I need the output like below

ITEM	Qty	BALANCE
I1	4	6
I2	0	10


What I have tried:

I write to join the 2 tables if I1 exists in Table1 then the balance will fetch as perfect.

Item which not exists in table1 is not displaying we need the item balance for the Item2 too in the output.

Kindly help
Posted
Updated 25-Jun-21 20:10pm

1 solution

Use GROUP BY to combine the items in Table1, and then JOIN them to Table2.
The GROUP BY can use aggregate functions - including SUM - to get the quantity per item.

SQL
SELECT a.Item, a.QTY, b.QTY - a.QTY AS Balance
  FROM (SELECT Item, SUM(QTY) AS QTY FROM Table1
        GROUP BY Item) a
  JOIN Table2 b ON a.Item = b.Item
   
Comments
Member 11658469 26-Jun-21 1:34am
   
This will fetch the balance where both tables contains.I need to get the item count from table2 which not exists in table 1
OriginalGriff 26-Jun-21 1:40am
   
I tested it with the input you provided, and compared it to the output you requested: it matches.

You need to explain yourself better, or provide sample data that actually shows what you want!

Member 11658469 26-Jun-21 1:49am
   
Thanks for the response.Sorry the exact example below

Table 1

ID ITEM QTY
1 I1 2
2 I1 2
3 I2 10


Table2

ID ITEM QTY
1 I1 10
2 I2 10
3 I3 10
I need the output like below

ITEM Qty BALANCE
I1 4 6
I2 10 0
I3 0 10

I3 exists in Table2 which not exists in Table1

The output should be mentioed above
OriginalGriff 26-Jun-21 2:12am
   
That's pretty simple - what did you try?
Sorry, I'd give you the solution - it only took a couple of seconds to do - but your attitude offends, so I want to be sure you aren't a lazy student trying to get me to do his homework ... :laugh:
Member 11658469 26-Jun-21 2:26am
   
Kindly help plz
OriginalGriff 26-Jun-21 3:12am
   
I will - but that doesn't mean we are here to do your homework for yoU!
So ... what have *you* tried?

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900