SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Enthusiast
    Join Date
    Oct 2004
    Location
    Uruguay
    Posts
    74
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Problem with 2 LEFT JOINS and GROUP BY

    Hi, I have a problem when I fetch data from 3 different tables:

    mainTable
    id
    ...

    ------------

    childTable1
    mainTableId
    childTable1Field1 (int)
    childTable1Field2 (int)
    ...

    ------------

    childTable2
    mainTableId
    ...

    What I want to fetch are some fields from mainTable and:
    1) SUM(childTable1.childTable1Field1) + SUM(childTable1.childTable1Field2) for for each mainTable.id
    2) COUNT(*) number of childTable2 records for each mainTable.id

    To accomplish this I wrote:

    Code:
    SELECT mainTable.id, 
    SUM(childTable1.childTable1Field1) + SUM(childTable1.childTable1Field2) AS myValue1,
    COUNT(childTable2.id) AS myValue2
    FROM mainTable
    LEFT JOIN childTable1 on childTable1.mainTableId = mainTable.id
    LEFT JOIN childTable2 on childTable2.mainTableId = mainTable.id
    GROUP BY mainTable.id
    From this query I'm getting wrong values, it seems that myValue1 is multiplied by myValue2...

    Do you know what's wrong here ?

    ps: when I write the same query but without one of the LEFT JOINS and not selecting COUNT or SUM, the query works, but of course I need both

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,245
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by ratamaster View Post
    Do you know what's wrong here ?
    you're getting cross join effects, because for each main id, you get multiple rows from one of the child tables, and each of those is joined with multiple rows from the other child table

    remove the GROUP BY and (temporarily) use SELECT * and you will see the multiplication effect on the number of matched rows

    the solution is to put at least one and preferably both of your aggregation operations into a subquery
    Code:
    SELECT mainTable.id
         , sub1.myValue1
         , sub2.myValue2
      FROM mainTable
    LEFT OUTER
      JOIN ( SELECT mainTableId
                  , SUM(childTable1Field1) + 
                    SUM(childTable1Field2) AS myValue1 
               FROM childTable1 
             GROUP
                 BY mainTableId ) AS sub1
        ON sub1.mainTableId = mainTable.id
    LEFT OUTER
      JOIN ( SELECT mainTableId
                  , COUNT(*) AS myValue2
               FROM childTable2 
             GROUP
                 BY mainTableId ) AS sub2
        ON sub2.mainTableId = mainTable.id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Oct 2004
    Location
    Uruguay
    Posts
    74
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you, it worked as I wanted to !


Bookmarks

Posting Permissions

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