Need to add a join here - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need to add a join here

Author

Topic

Adam West
Constraint Violating Yak Guru

261 Posts

Posted - 2009-09-18 : 09:00:47

Custnmbr is in the table Customerbudget and this is the parameter, but we only have customer name as the parameter. I need to have a join that will take the Customername from the user and locate the correct number keeping this SQL working correctly as it is:

SELECT Sum("SOP10200"."XTNDPRCE") XTNDPRCE, Sum("SOP10200"."QUANTITY") QUANTITY,
DATEADD(M,DATEDIFF(M,0,SOP10100.DOCDATE),0) ID, "IV40600"."UserCatLongDescr", "IV00101"."ITEMNMBR",
"IV00101"."ITEMDESC",AVG(CustomerBudget.BudgetAmount) as Budget

FROM (((("dbo"."SOP10100" "SOP10100" INNER JOIN "dbo"."SOP10200" "SOP10200"
ON "SOP10100"."SOPNUMBE"="SOP10200"."SOPNUMBE")
INNER JOIN
"dbo"."CustomerBudget" "CustomerBudget" ON "SOP10100"."CUSTNMBR"="CustomerBudget"."CUSTNMBR")
LEFT OUTER JOIN "dbo"."SOP10106" "SOP10106"
ON ("SOP10100"."SOPTYPE"="SOP10106"."SOPTYPE")
AND ("SOP10100"."SOPNUMBE"="SOP10106"."SOPNUMBE"))
INNER JOIN "dbo"."IV00101" "IV00101"
ON "SOP10200"."ITEMNMBR"="IV00101"."ITEMNMBR")
INNER JOIN "dbo"."IV40600" "IV40600"
ON ("IV00101"."ITMGEDSC"="IV40600"."USCATVAL")
AND ("CustomerBudget"."USCATVAL"="IV40600"."USCATVAL")

WHERE "CustomerBudget"."CUSTNMBR"=@CUSTNMBR
AND "SOP10100"."SOPTYPE"=1 and (SOP10100.DOCDATE between @FromDate and @ToDate)

group by DATEADD(M,DATEDIFF(M,0,SOP10100.DOCDATE),0), "IV40600"."UserCatLongDescr", "IV00101"."ITEMNMBR",
"IV00101"."ITEMDESC"
Order by iv40600.UserCatLongDescr, iv00101.ITEMDESC

Adam West
Constraint Violating Yak Guru

261 Posts

Posted - 2009-09-18 : 10:36:16

Another option would be a subselect:
WHERE "CustomerBudget"."CUSTNMBR"= (Select CustomerName from CustomerMaster

where customername= @custname)

On some machines, the query engine would most likely rewrite the subselect version of the statement into the inner join version. I don't know if MS SQL server does similar rewrites

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources