SQL Query--JOIN not the answer; need some insight 1

[edmana]

I am working on a query that pulls info from a table and then pulls sums from columns of other tables. When I join the first table, the sum works fine. When I join the second table, both sums now calculate totally incorrect. I believe this is because each join is returning multiple rows and they accumulate in the returned recordset before calculating.

Anyone have any ideas?

Thanks!
Ed

 

[gmmastros]

Can you show your query and some sample data that demonstrates your problem? This will make it easier for us to help you.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[edmana]

Sure!

Here is the first query (part of what will be a view):

SELECT dbo.tblProjects.ProjectKeyID, SUM(dbo.tvwrc_ProjectExpenses.TotalExpenses) AS Expr1
FROM dbo.tblProjects LEFT OUTER JOIN
dbo.tvwrc_ProjectExpenses ON dbo.tblProjects.ProjectKeyID = dbo.tvwrc_ProjectExpenses.ProjectKeyID
GROUP BY dbo.tblProjects.ProjectKeyID
HAVING (dbo.tblProjects.ProjectKeyID = 120020)

When I run the above, I get a value of 8570.21 which is correct.

However, when I add in another table/column I need the sum of, this is where it gets crazy. Here is the next query:

SELECT dbo.tblProjects.ProjectKeyID, SUM(dbo.tvwrc_ProjectExpenses.TotalExpenses) AS Expr1, SUM(dbo.tvwrc_ProjectLaborLogs.TotalCost) AS Expr2
FROM dbo.tblProjects LEFT OUTER JOIN
dbo.tvwrc_ProjectLaborLogs ON dbo.tblProjects.ProjectKeyID = dbo.tvwrc_ProjectLaborLogs.ProjectKeyID LEFT OUTER JOIN
dbo.tvwrc_ProjectExpenses ON dbo.tblProjects.ProjectKeyID = dbo.tvwrc_ProjectExpenses.ProjectKeyID
GROUP BY dbo.tblProjects.ProjectKeyID
HAVING (dbo.tblProjects.ProjectKeyID = 120020)


That nice 8570 cost turns into 5947725.74.

Any ideas?

Thanks!

 

[gmmastros]

The title of your question says that JOIN is not the answer. JOIN is the answer, but it is slightly more complicated than a simple join. In this case, you'll want to use derived tables (one for each table you are getting a sum from). These derived tables will join to the main project table. Like this:

Select dbo.tblProjects.*,
       [!]Expenses.[/!]Expr1,
       LaborLogs.Expr2
From   dbo.tblProjects
       Left Join [!]([/!]
          Select dbo.tvwrc_ProjectExpenses.ProjectKeyId, SUM(dbo.tvwrc_ProjectExpenses.TotalExpenses) AS Expr1
          From   dbo.tvwrc_ProjectExpenses
          Group By dbo.tvwrc_ProjectExpenses.ProjectKeyId
          [!]) As Expenses[/!] On dbo.tblProjects.ProjectKeyId = [!]Expenses.[/!]ProjectKeyId
       Left Join (
          Select dbo.tvwrc_ProjectLaborLogs.ProjectKeyId, SUM(dbo.tvwrc_ProjectLaborLogs.TotalCost) AS Expr2
          From   dbo.tvwrc_ProjectLaborLogs
          Group By dbo.tvwrc_ProjectLaborLogs.ProjectKeyId
          ) As LaborLogs on dbo.tblProjects.ProjectKeyId = LaborLogs.ProjectKeyId

Notice the parts outlined in red. See the parenthesis around that relatively simple query? The query within the parenthesis would be considered a derived table. Inside the parenthesis, you can have any query you want. Outside the parenthesis, you can think of this as 'just another table'. There are a couple of mandatory syntax elements for using a derived table. The parenthesis are required. You must give the derived table an alias ([!]As Expenses[/!]) and you must consistently use the alias through the rest of the query. This includes the select clause ([!]Expenses.Expr1[/!]) and the on clause ([!]Expenses.ProjectKeyId[/!]).

Lastly, I don't have your tables available to play with. There may be some minor problems with the query a wrote, but the concept behind the query is sound, and should work out very well for you.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

See a very similar problem here at MSDN - unfortunately I spent about 20 minutes trying to find that thread and failed...


PluralSight Learning Library

 

[edmana]

George,

That solution worked great! You are the man! Thanks so much for your quick response.

Markros, thanks for your help!

Ed

 

[edmana]

The plot thickens on this one.

I have gotten this to work with a bunch of different views, all reporting their totals back to the main view wonderfully!

However, one of the links comes back as null. I would expect this since no data was returned. However, I need to do addition and subtraction on this value. Any ideas how when the returned value is null, turn it into zero? I tried the Convert function, but no dice.

The code for this part looks like this:


(SELECT dbo.tvwrc_ProjectDiscountEstimated.ProjectKeyID, SUM(dbo.tvwrc_ProjectDiscountEstimated.TotalDollarsDiscounted)
AS DiscountEstimated
FROM dbo.tvwrc_ProjectDiscountEstimated
GROUP BY dbo.tvwrc_ProjectDiscountEstimated.ProjectKEYID) EstimatedDiscount ON
dbo.tblProjects.ProjectKeyID = EstimatedDiscount.ProjectKeyID

 

[gmmastros]

                          (SELECT     dbo.tvwrc_ProjectDiscountEstimated.ProjectKeyID, [!]Coalesce([/!]SUM(dbo.tvwrc_ProjectDiscountEstimated.TotalDollarsDiscounted)[!], 0)[/!]
                                                   AS DiscountEstimated
                            FROM          dbo.tvwrc_ProjectDiscountEstimated
                            GROUP BY dbo.tvwrc_ProjectDiscountEstimated.ProjectKEYID) EstimatedDiscount ON
                      dbo.tblProjects.ProjectKeyID = EstimatedDiscount.ProjectKeyID

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[edmana]

George,

That doesn't seem to be working for me. I believe what's happening is that the Select statement returned no value so the result is coming up as null, not the sum.

Any ideas?
Thanks!
Ed

 

[gmmastros]

I see. In that case, you'll need to modify the outer query (not shown in your most recent post). Put the coalesce on the out query.

Select dbo.tblProjects.*,
       [!]Coalesce([/!]Expenses.Expr1, [!]0) As Expr1,[/!]
       LaborLogs.Expr2
From   dbo.tblProjects
       Left Join (
          Select dbo.tvwrc_ProjectExpenses.ProjectKeyId, SUM(dbo.tvwrc_ProjectExpenses.TotalExpenses) AS Expr1
          From   dbo.tvwrc_ProjectExpenses
          Group By dbo.tvwrc_ProjectExpenses.ProjectKeyId
          ) As Expenses On dbo.tblProjects.ProjectKeyId = Expenses.ProjectKeyId
       Left Join (
          Select dbo.tvwrc_ProjectLaborLogs.ProjectKeyId, SUM(dbo.tvwrc_ProjectLaborLogs.TotalCost) AS Expr2
          From   dbo.tvwrc_ProjectLaborLogs
          Group By dbo.tvwrc_ProjectLaborLogs.ProjectKeyId
          ) As LaborLogs on dbo.tblProjects.ProjectKeyId = LaborLogs.ProjectKeyId

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[edmana]

George,

This worked great! Thanks so much!

Ed