August 11, 2015 at 8:08 am
Hi I thought adding left join would solve my problem but it doesn't seem to be the case.
What I have is two tables. Budgets on the left and Projects on the right. Project budgets are spent from the budget (Project.TotalBudget). If there are no records in the project (as budgets are created before a project) I still want to be able to retrieve the totalBudget in budget. Is there a way I can do this without fudging it?
SELECT dbo.Budget.TotalBudget, SUM(dbo.Project.TotalBudget) AS AllocatedBudget, dbo.Budget.TotalBudget - SUM(dbo.Project.TotalBudget) AS RemainingBudget
FROM dbo.Budget LEFT OUTER JOIN
dbo.Project ON dbo.Budget.BudgetID = dbo.Project.BudgetID
WHERE (dbo.Project.BudgetID = @BudgetID)
GROUP BY dbo.Budget.TotalBudget
August 11, 2015 at 8:26 am
Sure thing, move the WHERE clause to the ON clause and it'll work.
OUTER JOINs are kind of funny in that if you put filtering criteria in the WHERE clause for the OUTER table, it effectively turns it into an INNER JOIN. Moving that criteria to the ON clause, since, in this case, it's part of that clause, fixes it.
Although, you may get more than just one budget back. In which case, instead of putting the filter on the OUTER Project table, put it on the Budge table (and, in that case, back into the WHERE clause).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 11, 2015 at 8:33 am
Normally, Grant's remark should be on point, but here you need to change the column. I added a few other improvements that you might want to implement. Avoid 3 part column names inyour queries.
SELECT b.TotalBudget,
SUM(p.TotalBudget) AS AllocatedBudget,
b.TotalBudget - ISNULL( SUM(p.TotalBudget), 0) AS RemainingBudget --Added ISNULL
FROM dbo.Budget b
LEFT JOIN dbo.Project p ON b.BudgetID = p.BudgetID
WHERE b.BudgetID = @BudgetID --Changed column to the one from Budget table
GROUP BY b.TotalBudget
August 11, 2015 at 8:42 am
Thanks for the responses.
All I can say is Brilliant!
Luis I used you're solution and it worked like a charm!
August 11, 2015 at 8:49 am
Ah, you did the second thing I suggested. Cool.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply