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??
(Select field1, field2, joinfield1,joinfield2 FROM Table1 INNER JOIN Table2 ON Table1.field3 = Table2.field4 where field5=Value ) as A
(Select sum(agfield1) as agfield1, sum(agfield2) as agfield2,joinfield1,joinfield2 FROM Table3 INNER JOIN Table4 ON Table3.agfield3 = Table2.agfield4
Group By joinfield1,joinfield2) as B
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.