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,
ManolisYou 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.|||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|||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|||...
ON A.field1=B.joinfield1
AND A.field2=B.joinfield2|||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|||Uncanny coincidence!