# 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

• 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 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 it

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:

```<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 records As List(Of ABminus) = New List(Of ABminus)()

Dim record As ABminus = New ABminus()
End While
End If

conn.Close()

For Each item In records
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)

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 Thursday, October 7, 2021 12:00 AM
Monday, April 12, 2021 2:33 AM