Answered by:
Compare 2 columns

Question
-
User66371569 posted
hi
i have 2 tables A ,B
table A has 3 columns id line_id qty
Table B has 3 columns Id lin_ id qty
select sum(qty), line_id from A where id =2 group by line_id
select sum(qty) , line_id from B where id=2 group by line_id
note qty not one row ( line id more than one row) loop
what i want is in vb.net button click compare above sum qty id mismatched then
error message appears
"Line id 3 has mismatched qty please check it"
Hope you got it.
thank you
Wednesday, April 7, 2021 10:50 PM
Answers
-
User1535942433 posted
Hi thepast,
Could you solved this thread?If you still have problems,you could post to us.If it solved,you could mark these helpful answers.
Best regards,
Yijing Sun- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Monday, April 12, 2021 2:33 AM
All replies
-
User1535942433 posted
Hi thepast,
select sum(qty), line_id from A where id =2 group by line_id
select sum(qty) , line_id from B where id=2 group by line_id
I don't understand your problems. Id is the primary key. When you check id=2,it only select one row. So why you need to group by line_id?
Could you post your table and result to us?
Best regards,
Yijing Sun
Thursday, April 8, 2021 2:14 AM -
User66371569 posted
Table A
ID Item Qty Line ID 1 mmmmm 200 121 1 xxxxxxx 40 122 Table B
ID QTY Line ID 1 20 121 1 10 121 1 30 122 now we have fill table A and B
sow first i want to make sum of table B group by line id to compare it with table A
if sum(qty) of table B not equal to table A then
error sum of qty in table b not match qty in table a for item 'mmmm'
and if i fixed this sum code search for next row
and if there is any error then error appears witn item name
How can do it in vb.net button press
i want simple code
thank you
Thursday, April 8, 2021 11:58 AM -
User66371569 posted
I couldent solve it
Thursday, April 8, 2021 5:36 PM -
User1535942433 posted
Hi thepast,
thepast
I couldent solve itFirst,What this means? Do you haven't solved it?
Second, I have told you that if your ID is primary key,it can't have same value. However,according to your requirement, you need to group by ID and Line_ID. So, I have created a identity field.
Third,your details of requirement are not clear. If you have ID not 1 and Line ID are 121, how do you search?
I have created a demo with group by ID and Line ID. Just like this:
<asp:Button ID="Button1" runat="server" Text="Button" OnClick="Button1_Click" /> <asp:Label ID="Label1" runat="server" Text=""></asp:Label>
Class SurroundingClass Protected Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs) Dim strS, strSql As String strS = "Data Source=(localDb)\MSSQLLocalDB;Initial Catalog=aspnet-TestApplicationWithDatabase-20190820030542;Integrated Security=true" Dim conn As SqlConnection = New SqlConnection(strS) conn.Open() strSql = "select Id,SUM(QTY) as QTY,Line_ID from B group by Id,Line_ID except select ID,SUM(QTY) as QTY,Line_ID from A group by ID, Line_ID" Dim sqlCommand As SqlCommand = New SqlCommand(strSql, conn) Dim objRader As SqlDataReader = sqlCommand.ExecuteReader() Dim records As List(Of ABminus) = New List(Of ABminus)() If objRader.HasRows Then While objRader.Read() Dim record As ABminus = New ABminus() record.Id = Convert.ToInt32(objRader("Id").ToString()) record.Line_ID = Convert.ToInt32(objRader("Line_ID").ToString()) records.Add(record) End While End If conn.Close() For Each item In records Dim strSS, strSqlS As String strSS = "Data Source=(localDb)\MSSQLLocalDB;Initial Catalog=aspnet-TestApplicationWithDatabase-20190820030542;Integrated Security=true" Dim connS As SqlConnection = New SqlConnection(strSS) connS.Open() strSqlS = "select Item from A where ID='" & item.Id & "' and Line_ID='" + item.Line_ID & "'" Dim sqlCommandS As SqlCommand = New SqlCommand(strSqlS, connS) Dim objRaderS As SqlDataReader = sqlCommandS.ExecuteReader() If objRaderS.HasRows Then While objRaderS.Read() Label1.Text += JsonConvert.SerializeObject(objRaderS("Item").ToString()) End While End If Next End Sub Public Class ABminus Public Id As Int32 Public Line_ID As Int32 End Class End Class
TableA:
TableB:
Result:
Best regards,
Yijing Sun
Friday, April 9, 2021 6:44 AM -
User66371569 posted
Thank you so much for ur helping as usual
I will test and inform you
Friday, April 9, 2021 7:52 AM -
User-1716253493 posted
USE SQL
SELECT CASE WHEN SUM(Table_A.QTY) = SUM(Table_B.QTY) THEN 1 ELSE 0 END AS matchstatus FROM Table_A INNER JOIN Table_B ON Table_A.id = Table_B.ID AND Table_A.[Line ID] = Table_B.[Line ID] GROUP BY Table_A.id, Table_A.[Line ID] HAVING (Table_A.id = @ID)
Friday, April 9, 2021 4:23 PM -
User1535942433 posted
Hi thepast,
Could you solved this thread?If you still have problems,you could post to us.If it solved,you could mark these helpful answers.
Best regards,
Yijing Sun- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Monday, April 12, 2021 2:33 AM