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,joinfield2The 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|||your question is a bit hard to understand, but it looks like you want those two queries left joined

that's easy --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.joinfield2note i fixed an apparent typo|||thanks, you really understood exactly what I wanted.
The sql statement worked fine.

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

Need a little help with Join (Beginner)

Im writing this stored procedure and just need to add one more column to the mix. But i cant recall exactly what to do with joins. I have examples, but they all look alittle different. Anyways, i realized that the column i want to add (its highlighted) Needs to have a join. Here is my code. Can anyone explain to me, how i join this with the rest. IT is located in a table called Budget_Reporting_Group and the column is called Budget_Reporting_Group_Description. As you can see, I have another Table called Salesperson_Purchaser, Im pretty sure these two tables are relational by a Salesperson_Purchaser_ID. How would i do the join? If anything, can you display what it would look like (IE yourDatabase.dbo.TableName as Table_Name ON etc etc) I completely forgot the format.

USE [RC_STAT]

GO

/****** Object: StoredProcedure [dbo].[PROC_RPT_EXPENSE_DETAIL_T&E_MKT_Copy] Script Date: 06/28/2007 09:49:08 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTERPROCEDURE [dbo].[PROC_RPT_EXPENSE_DETAIL_T&E_MKT_COPY]

(@.Region int=Null)

AS

BEGIN

SELECT

[Item_Ledger_Posting_Datetime] Post_Date

,'SMP' Budget_Type

,'Invoice'as Document_Type

,[Item_Ledger_Document_No] Document_Number

,[Item_Description]+' '+'('+[Item_No]+')' Entry_Description

,ISNULL(RC_STAT.dbo.udf_Correct_Price(Item_No, Item_Ledger_Posting_Datetime,'SALESAMP')*-1*Item_Ledger_Invoiced_Qty,Item_Ledger_Cost_Posted_GL *-1 ) Amount

,-1*[Item_Ledger_Invoiced_Qty] Quantity

,Customer_Name

,'' External_Doc_no

,[Item_Ledger_Sales_Responsible] SR_Code

,[Item_Ledger_Mars_Period_Code] ThePeriod

,[Item_Ledger_Mars_Year] TheYear

,Territory.Name AS Territory_Name

,Region.Region AS Region_Name

,Budget_Reporting_Group.Budget_Reporting_Group_Description

FROM [NavisionReplication].[dbo].[Qry_Item_Ledger_Detail]

INNERJOIN NavisionReplication.dbo.Tbl_Salesperson_Purchaser AS Salesperson_Purchaser

ON [Item_Ledger_Sales_Responsible] = Salesperson_Purchaser.SalesPerson_Purchaser_Code

INNERJOIN RC_DWDB_INSTANCE_1.dbo.Territory AS Territory

ON Territory.Code = Salesperson_Purchaser.Territory_Code

INNERJOIN NavisionReplication.dbo.Tbl_Budget_Reporting as Budget_Reporting

ON Salesperson_Purchaser.Salesperson_Purchaser_Code=Budget_Reporting.Salesperson_Purchaser_Code

LEFTOUTERJOIN RC_DWDB_INSTANCE_1.dbo.Region AS Region

ON Territory.Region_Key = Region.Region_Key

WHERE Region.Region_Key = @.Region

AND [Item_Ledger_Mars_Year] = 2007

UNIONALL

SELECT

[GL_Entry_Posting_DateTime]

,[Budget_Type_Code]

,[Document_Type]

,[GL_Entry_Document_No]

,[GL_Entry_Description]

,[GL_Entry_Amount]

,0 Quantity

,[User_ID]

,[GL_Entry_External_Document_No]

,[Sales_Responsible]

,[Mars_Period_Code]

,[Mars_Year]

,Territory.Name AS Territory_Name

,Region.Region AS Region_Name

,Budget_Reporting_Group.Budget_Reporting_Group_Description

FROM [NavisionReplication].[dbo].[Tbl_GL_entry] GL_entry

INNERJOIN [RC_DWDB_INSTANCE_1].[dbo].[Tbl_Budget_Accounts] Budget_Accounts

ON Budget_Accounts.[GL_Account_No] = GL_entry.[GL_Account]

INNERJOIN NavisionReplication.dbo.Tbl_Salesperson_Purchaser AS Salesperson_Purchaser

ON GL_entry.[Sales_Responsible] = Salesperson_Purchaser.SalesPerson_Purchaser_Code

INNERJOIN RC_DWDB_INSTANCE_1.dbo.Territory AS Territory

ON Territory.Code = Salesperson_Purchaser.Territory_Code

LEFTOUTERJOIN RC_DWDB_INSTANCE_1.dbo.Region AS Region

ON Territory.Region_Key = Region.Region_Key

INNERJOIN NavisionReplication.dbo.Tbl_Budget_Reporting as Budget_Reporting

ON Salesperson_Purchaser.Salesperson_Purchaser_Code=Budget_Reporting.Salesperson_Purchaser_Code

WHERE GL_entry.[Mars_Year] = 2007

END

Code Snippet

INNER JOIN Budget_Reporting_Group

ON BUDGET_REPORTING_GROUP.Salesperson_Purchaser_ID = SALESPERSON_PURCHASER.Salesperson_Purchaser_ID

|||

I still keep getting this error:

sg 208, Level 16, State 1, Procedure PROC_RPT_EXPENSE_DETAIL_T&E_MKT_COPY, Line 7

Invalid object name 'Tbl_Budget_Reporting_Group'.

Msg 208, Level 16, State 1, Procedure PROC_RPT_EXPENSE_DETAIL_T&E_MKT_COPY, Line 7

Invalid object name 'Tbl_Budget_Reporting_Group'.