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
ASP.NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today. Learn more >
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:
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
ASP.NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today. Learn more >
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)
Programming to simplify, don't look for difficult way
Suwandi - Non Graduate Programmer
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
ASP.NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today. Learn more >
Member
7 Points
137 Posts
Compare 2 columns
Apr 07, 2021 10:50 PM|thepast|LINK
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
Contributor
3990 Points
1562 Posts
Re: Compare 2 columns
Apr 08, 2021 02:14 AM|yij sun|LINK
Hi thepast,
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
Member
7 Points
137 Posts
Re: Compare 2 columns
Apr 08, 2021 11:58 AM|thepast|LINK
Table A
Table B
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
Member
7 Points
137 Posts
Re: Compare 2 columns
Apr 08, 2021 05:36 PM|thepast|LINK
I couldent solve it
Contributor
3990 Points
1562 Posts
Re: Compare 2 columns
Apr 09, 2021 06:44 AM|yij sun|LINK
Hi thepast,
First,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:
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 ClassTableA:
TableB:
Result:
Best regards,
Yijing Sun
Member
7 Points
137 Posts
Re: Compare 2 columns
Apr 09, 2021 07:52 AM|thepast|LINK
Thank you so much for ur helping as usual
I will test and inform you
All-Star
52813 Points
15767 Posts
Re: Compare 2 columns
Apr 09, 2021 04:23 PM|oned_gk|LINK
USE SQL
Suwandi - Non Graduate Programmer
Contributor
3990 Points
1562 Posts
Re: Compare 2 columns
Apr 12, 2021 02:33 AM|yij sun|LINK
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