Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2009
    Posts
    9

    Unanswered: SQL select statement

    Hello all, I have the following SQL statement in my vb.net program:
    Statement 1
    Code:
     sql = "SELECT Trn_PO.POID, Trn_PO.PONo, "
                    sql &= "Tb_Company.ShortName + ' - ' + (SELECT  Tb_address.Name FROM Tb_Address WITH (NOLOCK) WHERE GLN = Trn_PO.ShipToGLN) AS Sender,"
                    sql &= " (SELECT  Tb_address.GLN FROM Tb_Address WITH (NOLOCK) WHERE GLN = Trn_PO.ShipToGLN) AS GLN, "
                    sql &= " Trn_PO.BuyerID, Trn_PO.CreatedDt, Trn_PO.DeliveryDt, Trn_PO.PODt, Trn_PO.Status,   Tb_Company.ShortName "
                    sql &= " , CASE WHEN  Trn_PO.IsArchived = 0 THEN 'Open' ELSE 'Closed' END AS IsComplete, "
                    sql &= "Tb_Company_Module.FormName  "
                    sql &= "FROM Trn_PO WITH (READPAST) , Tb_Company WITH (NOLOCK), Tb_Company_Module  WITH (NOLOCK) "
                    sql &= "LEFT JOIN Tb_Role_MessageStore RM  WITH (NOLOCK) ON RM.ModuleName = Tb_Company_Module.ModuleName  "
                    sql &= "AND RM.SellerID = Tb_Company_Module.SellerID  "
                    sql &= "AND RM.BuyerID = Tb_Company_Module.BuyerID  "
                    sql &= "WHERE Trn_PO.BuyerID = Tb_Company.CompanyID "
                    sql &= "AND Tb_Company_Module.BuyerID = Trn_PO.BuyerID  "
                    sql &= "AND Tb_Company_Module.SellerID = Trn_PO.SellerID  "       
                    sql &= "AND Trn_PO.SellerID='" + sellerID + "' "
    
                    'start filtering
                    If buyerID.Length > 0 Then
                        sql &= "AND Trn_PO.BuyerID='" + buyerID + "' "
                    End If
    
                    If PONo.Length > 0 Then
                        sql &= "AND Trn_PO.PONo " + SQLStr(PONo, SQLDataTypes.Dt_LikeEnd) + ""
                    End If
    
                    If Status.Length > 0 Then
                        sql &= " AND Trn_PO.Status in ('" + Status + "') "
                    End If
    
                    If IsComplete.Length > 0 Then
                        sql &= IIf(IsComplete = "Open", "AND Trn_PO.IsArchived = 0 ", "AND Trn_PO.IsArchived = 1 ")
                    End If
    
                    'filter by branch
                    If (ddlSender.Enabled = True And ddlSender.Text = "ALL") Then
                        'MessageBox(ddlSender.Text)
                        sql &= " AND Trn_PO.BuyerID='" + UserCompanyID + "'"
                    ElseIf (ddlSender.Enabled = True And ddlSender.Text <> "ALL") Then
    
                        Dim strGLN As String = ""
                        Dim intIndex As Integer = 0
                        Dim strCheck As String = "-"
    
                        intIndex = ddlSender.SelectedValue.IndexOf(strCheck)
                        strGLN = ddlSender.SelectedValue.Substring(intIndex + 1)
                        sql &= " AND Trn_PO.BuyerGLN='" + strGLN + "'"
                    End If
    
                    If strDateType = "PO Date" Then
                        sql &= " AND Trn_PO.PODt >= " & SQLStr(strFromDate, SQLDataTypes.Dt_Date) & "  AND Trn_PO.PODt < DATEADD(DAY, 1,  " & SQLStr(strToDate, SQLDataTypes.Dt_Date) & ") "
                    ElseIf strDateType = "Delivery Date" Then
                        sql &= " AND Trn_PO.DeliveryDt >= " & SQLStr(strFromDate, SQLDataTypes.Dt_Date) & " AND Trn_PO.DeliveryDt < DATEADD(DAY, 1,  " & SQLStr(strToDate, SQLDataTypes.Dt_Date) & ") "
                    ElseIf strDateType = "Received Date" Then
                        sql &= " AND Trn_PO.CreatedDt >= " & SQLStr(strFromDate, SQLDataTypes.Dt_Date) & "  AND Trn_PO.CreatedDt < DATEADD(DAY, 1,  " & SQLStr(strToDate, SQLDataTypes.Dt_Date) & ") "
                    End If
    
                    sql &= " AND ISNULL(Trn_PO.IsDeleted,0) = 0 "
                    sql &= " AND Tb_Company_Module.ModuleName = '" & strPurchaseOrder & "' "
                    sql &= " AND Trn_PO.ChgAmt11 = 0"
    
                    'Filter by User Role
                    sql &= " AND RM.RoleID IN (" & RoleIDString() & ") "
                    'sql &= " ORDER BY Trn_PO. CreatedDt DESC "
    
                    If strSort.Length > 0 Then
                        sql &= " ORDER BY " + strSort + ""
                    Else
                        sql &= " ORDER BY Trn_PO.CreatedDt DESC "
                    End If
    In my table, for example of PO number 1234, it may contain two rows of data with one is chgamt11=0 and another is chgamt11=1. The above statement mainly to retrieve data with chgamt11=0. And now I have the following select statement to retrieve the data with chgamt11=1.
    Statement 2
    Code:
    Select Createddt As [Document Date] from trn_po where chgamt11=1
    If I using the two select statement separately, it will display result like this:


    PO Number CreatedDate (Statement 1)
    1234 05/03/2010

    PO Number Document Date (Statement 2)
    1234 01/03/2010



    How can I combine two statement together so that it will display the result like this:


    PO Number CreatedDate Document Date
    1234 05/03/2010 01/03/2010

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    That would be something that might be called "self-join":
    Code:
    select t1.pono, t1.createddt as "created date", t2.createddt as "document date"
    from trn_po t1, trn_po t2
    where t1.pono = "whatever" 
      and t1.pono = t2.pono
      and t1.chgamt11 = 0
      and t2.chgamt11 = 1
      ...
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Apr 2009
    Posts
    9
    Hi, the statement is worked. But it will only retrieve the data which contain two same PO Number from my table. How about if some of the PO Number only contain one record in my table?

  4. #4
    Join Date
    Apr 2009
    Posts
    9
    Hi, actually there are two scenario here. One is the PO Number contain two different data which I want to combine them into one. For example,

    PO Number Createddt DocumentDt
    1234 01/03/2010 NULL
    1234 NULL 05/03/2010


    After combine into one,
    PO Number Createddt DocumentDt
    1234 01/03/2010 05/03/2010


    Another scenario is the PO Number only contain one record.

    PO Number Createddt DocumentDt
    1235 02/03/2010 NULL


    The statement that you suggested to me only worked to retrieve the first scenario data where PO Number contain two different data but it not able to retrieve second scenario data.


    KIndly advice. Thanks.

  5. #5
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Take a look at left join.
    Dave

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •