Left Outer Join on 3 tables - Incorrect Aggregate Results

• david.marron-916288

  SSC Journeyman

  Points: 90

  More actions

  February 27, 2012 at 4:19 pm

  #253698

  I'm not sure if I've made an error or I've found a bug:

  The following joins 3 tables to return columns from main table A and aggregate values from Table B and C.

  When I limit the statement below to tables A&B's cols/join, I get the correct results on the aggregate cols from B, but adding C's cols (and join) produces different results for B's aggregate cols. Really confused why...

  Any ideas where it's going wrong?

  SELECT A.idEHSWeeklyReports, A.ProjectID, A.ReportWeekNo, A.ReportStartDate, A.ReportFinishDate, A.IsDeleted,

  isnull(Sum(B.NumberOfIncidentsSubcontractor),0) AS NumberOfLostTimeIncidentsSubcontractor,

  isnull(Sum(B.LostTimeDaysSubcontractor),0) AS LostTimeDaysSubcontractor,

  isnull(Sum(B.NumberOfIncidentsADCO),0) AS NumberOfLostTimeIncidentsADCO,

  isnull(Sum(B.LostTimeDaysADCO),0) AS LostTimeDaysADCO,

  isnull(Sum(C.NumberOfIncidentsSubcontractor),0) As NumberOfNonLostTimeIncidentsSubcontractor,

  isnull(Sum(C.NumberOfIncidentsADCO),0) As NumberOfNonLostTimeIncidentsADCO

  FROMdbo.EHSWeeklyReportsRegister A

  LEFT OUTER JOIN

  (SELECT WeeklyReportID, NumberOfIncidentsSubcontractor, LostTimeDaysSubcontractor, NumberOfIncidentsADCO, LostTimeDaysADCO

  FROM dbo.LostTimeIncidents

  WHERE Isdeleted = 0) B

  ON dbo.EHSWeeklyReportsRegister.idEHSWeeklyReports = B.WeeklyReportID

  LEFT OUTER JOIN

  (SELECT WeeklyReportID, NumberOfIncidentsSubcontractor, NumberOfIncidentsADCO

  FROM dbo.EHSNonLostTimeOHandSIncidents

  WHERE IsDeleted =0) C

  ON dbo.EHSWeeklyReportsRegister.idEHSWeeklyReports = C.WeeklyReportID

• mister.magoo

  SSC-Forever

  Points: 47174

  More actions

  February 27, 2012 at 4:54 pm

  #1452340

  A1 --> B1

  A2 --> B2

  Count B = 2

  A1 --> B1 --> C1

  A1 --> B1 --> C2

  A2 --> B2 --> C3

  Count B = 3

  What I mean : Row 1 in A joins to 1 Row in B and 2 Rows in C. Result = 2 Rows, duplicate A, duplicate B

  Not the only possibility, but likely.

  ;p)

  Edit: Oh and use the Alias "A" - you declared it after all.

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]