Results 1 to 7 of 7

Thread: Complex Query

  1. #1
    Join Date
    May 2004
    Posts
    34

    Cool Unanswered: Complex Query

    Hi,
    I want to create a query with which I must compare the production demands with the production results. The production demands can be get by the join of two tables. The production results can be get from an aggregate of 4 tables. The connection of these two objects rely on two fields that exist in both two objects. In order to show all the production demands I must left join the two fields from the demands object to the two fields exist in the aggegate production object. In MsAccess the only way to do it is to create 2 queries one for the demands and one aggregate for the production and in a third query create two left joins from the demands query to the production query and get the right results. How can I do it with MSSQL Server with a query??

    Best Regards,
    Manolis

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You could do this the same way in SQL Server by creating separate VIEWS (equivalent of MS Access Queries) for your two sub-sets and then joining them in a third view or stored procedure.

    More efficient, though more difficult to code, would be to write the entire thing as a single TSQL Statement with subqueries.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    May 2004
    Posts
    34

    need a single sql statement

    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), sum(agfield2),joinfield1,joinfield2 FROM Table3 INNER JOIN Table4 ON Table3.agfield3 = Table2.agfield4
    where agfield5=Value
    Group By joinfield1,joinfield2

  4. #4
    Join Date
    Aug 2004
    Location
    Calgary, Alberta
    Posts
    106
    SELECT field1,field2,agfield1,agfield2 FROM

    (Select field1, field2, joinfield1,joinfield2 FROM Table1 INNER JOIN Table2 ON Table1.field3 = Table2.field4 where field5=Value ) as A
    INNER JOIN
    (Select sum(agfield1) as agfield1, sum(agfield2) as agfield2,joinfield1,joinfield2 FROM Table3 INNER JOIN Table4 ON Table3.agfield3 = Table2.agfield4
    where agfield5=Value
    Group By joinfield1,joinfield2) as B

    ON A.field1=B.joinfield1
    WHERE A.field2=B.joinfield2


    That should do it. You may want to switch between field1 and field2 as the inner join condition will perform better with a field that is more specific.

    Cheers,
    -Kilka

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ....
    ON A.field1=B.joinfield1
    AND A.field2=B.joinfield2
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    left joins, the man wanted left joins

    why, his question is almost exactly the same as what this other guy wanted in this other thread!! --

    http://www.dbforums.com/t1118727.html
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Uncanny coincidence!
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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