Results 1 to 4 of 4
  1. #1
    Join Date
    May 2004
    Posts
    34

    Unanswered: need a single sql statement

    How can I do it by using two subqueries the second of them to be aggregate and have two left joins from the first to the second??

    e.g. How can I left join these two queries with the joinfield1,joinfield2 fields??

    1st query
    Select field1, field2, joinfield1,joinfield2 FROM Table1 INNER JOIN Table2 ON Table1.field3 = Table2.field4 where field5=Value

    2nd query
    Select sum(agfield1) As f1, sum(agfield2) As f2, joinfield1,joinfield2 FROM Table3 INNER JOIN Table4 ON Table3.agfield3 = Table2.agfield4
    where agfield5=Value
    Group By joinfield1,joinfield2

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    The simplest would be to cut and paste, but you'll have to verify the accuracy of the final result:

    Select field1, field2, joinfield1,joinfield2 FROM Table1 INNER JOIN Table2 ON Table1.field3 = Table2.field4 where field5=Value
    inner join (Select sum(agfield1) As f1, sum(agfield2) As f2, joinfield1,joinfield2 FROM Table3 INNER JOIN Table4 ON Table3.agfield3 = Table2.agfield4
    where agfield5=Value
    Group By joinfield1,joinfield2) x
    on table1.joinfield1=x.joinfield1
    and table1.joinfield2=x.joinfield2
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    your question is a bit hard to understand, but it looks like you want those two queries left joined

    that's easy --
    Code:
    select curly.*
         , larry.*
      from ( 
           select field1
                , field2
                , joinfield1
                , joinfield2 
             from Table1 
           inner 
             join Table2 
               on Table1.field3 
                = Table2.field4 
            where field5 = Value
           ) as curly
    left outer
      join (
           select sum(agfield1) as f1
                , sum(agfield2) as f2
                , joinfield1 
                , joinfield2 
             from Table3 
           inner 
             join Table4 
               on Table3.agfield3 
                = Table4.agfield4 
           where agfield5 = Value
           group 
              by joinfield1
               , joinfield2
           ) as larry
        on curly.joinfield1 = larry.joinfield1
       and curly.joinfield2 = larry.joinfield2
    note i fixed an apparent typo
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    May 2004
    Posts
    34

    thanks r937

    thanks, you really understood exactly what I wanted.
    The sql statement worked fine.

    Thanks very much for your help.
    Best Regards,
    Manolis Perrakis

Posting Permissions

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