locked
Compare 2 columns RRS feed

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

    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