Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2004
    Posts
    21

    Unanswered: Help with select joins

    Hi, Im not so experienced in SQL, Im stuck
    with something I need for a homework so I have
    this situation:

    I have a master table and two child tables
    as follows:

    Master table: Invoices (pk= inumber)
    Child table #1: Client Payments (fk= inumber)
    Child table #2: Client credit notes (fk= inumber)


    I need to get how much is left for each invoice,
    so I know how much each client owes me.

    The pseudo formula is something like this:

    Invoice amount - (Payments total sum + Credit notes total sum)

    So the SQL I made for now only gets correctly this part
    of the formula:

    Invoice total - Payments total

    The SQL is as follows:

    select invoices.inumber,invoices.amount-sum(payments.amount)
    from invoices
    left join payments on invoices.inumber=payments.inumber
    where invoices.status='not paid'
    group by invoices.inumber;

    (by the way: Im using MySQL)

    But now Im stuck for 4 days modifying the SQL and now Im
    desperate and my brain is going to explode.

    Thanx very very very much in advance.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The LEFT OUTER join is the lynchpin to your problem. Use a LEFT OUTER from master to each of your child tables, and you should be rocking and rolling in no time!

    BTW, thanks for being up-front about this being an assignment. It is refreshing to see a student with a smidgeon of integrity, since there are SO many students that try to pass their homework off as work!

    -PatP

  3. #3
    Join Date
    Jun 2004
    Posts
    21

    Thanx, Ill test it

    Thanks a lot Pat,
    ill test it tonight,
    and thanks for your words!!

    Roland

  4. #4
    Join Date
    Jun 2004
    Posts
    21

    Weird results

    Hi, Ive tested LEFT OUTER in this way (to verify
    that the correct sums are displayed):

    select invoices.inumber,invoices.amount,sum(payments.amou nt),
    sum(creditnotes.amount)
    from invoices
    left outer join payments on invoices.inumber=payments.inumber
    left outer join creditnotes on invoices.inumber=creditnotes.inumber
    where invoices.status='not paid'
    group by invoices.inumber;

    But there's a weird situation, an example results displayed are:

    inumber sum(payments.amount) sum(creditnotes.amount)
    1 20 6
    2 30 12
    ...

    but the real results must be:
    inumber sum(payments.amount) sum(creditnotes.amount)
    1 10 3
    2 10 4
    ...

    In invoice 1 there's only one payment of $10 and two credit notes
    that sum $3, i think mysql is showing results x 2 because there are two records of credit notes.
    The same situation happens in invoice 2: there are three payments
    that sum $10 and one credit note that sum $4, and because there
    are three records of payments the results displayed are x 3.

    How can I avoid this situation? I think Im really close to the end,
    and I will be rocking and rolling!

    Thanks in advance

    Roland

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Ouch! This is one place where the limitations of MySQL hurt me. The lack of a correlated sub-query is a killer for this kind of processing using only standard SQL. There may be some MySQL specific solution that will allow you to solve this using a simple select, but I don't know how to do it.

    The best answer I can offer is to use a temporary table. I think that you'll need to:

    1) populate the temp table using the amount and the sum of the payments for all of the invoice numbers that have a status of 'not paid'
    2) LEFT OUTER JOIN that temporary table to the credits to produce the final result.

    Uff-da! I need to think on this one a bit. It seems like a nasty shortcoming to me.

    -PatP

Posting Permissions

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