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

Answer 1

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

Answer 2

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

Answer 3

User66371569 posted

I couldent solve it

Answer 4

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

Answer 5

User66371569 posted

Thank you  so much  for ur helping   as usual

I will test and inform you

Answer 6

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)

Answer 7

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