Somthing wrong with 3 Tables SQL Statment

databases Ukraine
  • 11 years ago
    hello everyone
    So cold now in Russia, about    -20C with strong icing wind. The best now to stay at home and do something useful. …so it is what am struggling with -

    Advise someone, please to solve the problem with SQL.
    Below the simplified tables from database, this part of base is to store information on bargains

    Three tables:
    1.    (The main table labeling bargains)

    TblBargain
       IDBargain
       BargainInformation
       CompanyName

    2.    (The table registering payments on the bargain)

    TblPayments
       IDPayment
       PaymentSum
       IDBargain

    3.    (The table registering dispatches on the bargain)

    TblDispatches
       IDDispatch
       DispatchSum
       IDBargain

    And SQL statement to have for each bargain TotalDispatches, TotalPayments and Balance in between.

    Ado.RecordSource =
    "SELECT TblBargain.CompanyName, TblBargain.IDBargain, " & _
    “TblBargain. BargainInformation, " & _
    “SUM(TblDispatches.DispatchSum) as TotalDispatchSum, " & _
    "SUM(TblPayments.PaymentSum) as TotalPaymentSum, (TotalDispatchSum - TotalPaymentSum) as Balance " & _
                       "FROM (TblBargain LEFT JOIN TblDispatches ON TblBargain.IDBargain =                  TblDispatches. IDBargain) " & _
                       "LEFT JOIN TblPayments ON TblBargain.IDBargain = TblPayments. IDBargain " & _
                       "WHERE [TblBargain.CompanyName] LIKE '" & strCompanyName & "'" & _
     "GROUP BY TblBargain.CompanyName, TblBargain.IDBargain, TblBargain. BargainInformation "

    The PROBLE-E-EM! I-I-IS that this statement works correct in the cases when tables Dispatch- and Payment- have only one rec. in each table or only one of the tables above have records (could be more then one). Otherwise it sums the records in first indicated table (TblDispatches) and duplicates the sum with each record in the second table (TblPayments). Thus instead of one resulting record for one chosen bargin in the interface table I have several ones.
  • 11 years ago
    Try to use this query it will give u results as expected, may be some syntax error will be in it as i have not tested just made it....

    SELECT B.CompanyName, B.IDBargain, B. BargainInformation,
    D.TD as TotalDispatchSum,
    P.TP as TotalPaymentSum,
    (D.TD - P.TP) as Balance

    FROM TblBargain B

       LEFT JOIN (SELECT IDBargain , ISNULL(SUM(PaymentSum),0) AS TP FROM TblPayments
       GROUP BY IDBargain) AS P
       ON B.IDBargain = P.IDBargain

       LEFT JOIN (SELECT IDBargain , ISNULL(SUM(DispatchSum),0) AS TD FROM TblDispatches
       GROUP BY IDBargain) AS D
       ON B.IDBargain = D.IDBargain

    Regards
  • 11 years ago

    Hello MJaved,


    thank u for assistance indeed. I will try it up.


    Regards


    Holmes, Moscow.

  • 11 years ago

    Hello MJaved and everyone,


    The blocks "LEFT JOIN ..." separately works perfect but up to now i have not managed to find some missing operand to make these two blocks work together. Someone help?...


    Thanks in advance.

Post a reply

Enter your message below

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“Some people, when confronted with a problem, think "I know, I’ll use regular expressions." Now they have two problems.” - Jamie Zawinski