www.webdeveloper.com
Results 1 to 10 of 10

Thread: Problem with SQL Query in vb.net

  1. #1
    Join Date
    Feb 2007
    Posts
    6

    Problem with SQL Query in vb.net

    SELECT
    KSID, Client, LOCATION, Finisher, NPB, sort_adj, SHIPDATE, numb, pay, P_Charge, Extra_Charge, Weight_Fee, WEIGHT, DEAD_PAY, DEAD_WEIGHT, DEAD, SLOWS_PAY, SLOWS_WEIGHT, NPPC, SLOWS, RESALES_PAY, RESALES_WEIGHT, RESALES, SLOW_NPPC, SLOW_NPB, slow_weight_fee, resale_nppc, resale_npb, salecode.Customer, salecode.SaleType, salecode.SALECODE, salecode.SALESACCT, salecode.[desc]

    FROM
    Invoice LEFT JOIN SALECODE ON Invoice.Client = salecode.customer

    This select statement is returning the correct data(4 records for each invoice.ksid), but that causes a problem. The SALECODE table contains data used by an accounting program, and each record in the Invoice table can match 4 times in the SALECODE table. For example, the SALECODE table might have 4 records in it with the same customer field, but just a different salecode.

    So it could be like this

    Customer SaleCode

    ClientA W001
    ClientA W002
    ClientA W003
    ClientA W004
    ClientB W001
    ClientB W002
    ClientB W003
    ClientB W004

    Since my join finds all 4 matches, each query is returning 4 times the data that is needed. This is all used for an export file, and my actual code is doing all the data writing. The only fields I can join on are invoice.Client and Salecode.Customer. Is there a way to narrow it down to only returning 1 value for each invoice ID?

  2. #2
    Join Date
    Dec 2006
    Location
    Escaz˙ (Costa Rica) and Mallorca (Spain)
    Posts
    3,234
    Use:
    GROUP BY invoice ID (to ensure you only get one record) and
    ORDER BY SaleCode (to ensure you get the lowest Salecode)

  3. #3
    Join Date
    Feb 2007
    Posts
    6
    By adding the group by clause, I get the following error

    Column Invoice.Client is invalid in the select list because it is not contained in either an aggregate function or a group by clause

  4. #4
    Join Date
    Dec 2006
    Location
    Escaz˙ (Costa Rica) and Mallorca (Spain)
    Posts
    3,234
    Add COUNT(*) in the SELECT part of the statement.

  5. #5
    Join Date
    Feb 2007
    Posts
    6
    Not sure I am following. Is this what you mean?

    SELECT Count(*) AS Test, KSID, Client, LOCATION, Finisher, NPB, sort_adj, SHIPDATE, numb_head, pay, Plant_Charge, Extra_Charge, Weight_Fee, WEIGHT, DEAD_PAY, DEAD_WEIGHT, DEAD, SLOWS_PAY, SLOWS_WEIGHT, NPPC, SLOWS, RESALES_PAY, RESALES_WEIGHT, RESALES, SLOW_NPPC, SLOW_NPB, slow_weight_fee, resale_nppc, resale_npb, salecode.Customer, salecode.SaleType, salecode.SALECODE, salecode.SALESACCT, salecode.[desc]

    FROM INVOICE LEFT JOIN SALECODE ON INVOICE.CLIENT = salecode.customer

    Group By Invoice.KSID
    Order By SaleCode.SaleCode

  6. #6
    Join Date
    Dec 2006
    Location
    Escaz˙ (Costa Rica) and Mallorca (Spain)
    Posts
    3,234
    Yes, that's what I meant.

  7. #7
    Join Date
    Feb 2007
    Posts
    6
    exact same error message

  8. #8
    Join Date
    Dec 2006
    Location
    Escaz˙ (Costa Rica) and Mallorca (Spain)
    Posts
    3,234
    What DBMS are you using?

  9. #9
    Join Date
    Feb 2007
    Posts
    6
    SQL Server 2005

  10. #10
    Join Date
    Dec 2006
    Location
    Escaz˙ (Costa Rica) and Mallorca (Spain)
    Posts
    3,234
    Ok. The above works on MySQL. You may have to tweak it for MSSQL

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles