Two LEFT JOINS multiply my values

[JimmyFo]

If I run this code, I get the proper results:

    SELECT 
		SUM(CASE WHEN MS.strMilestoneStatus = 'GREEN' THEN 1 ELSE 0 END) [COUNT_GREEN],
        SUM(CASE WHEN MS.strMilestoneStatus = 'YELLOW' THEN 1 ELSE 0 END) [COUNT_YELLOW],
        SUM(CASE WHEN MS.strMilestoneStatus = 'RED' THEN 1 ELSE 0 END) [COUNT_RED]
	FROM tblProject AS P
		--JOIN for milestone statuses
		LEFT JOIN tblProjectMilestone as MS
			ON P.intProjectID = MS.int_fk_ProjectID
    WHERE
		(11 IS NULL OR P.intProjectID = 11)

Results: 3 4 1

If I run this one, I get the proper results:

    SELECT 
		SUM(CASE WHEN PB.strBudgetStatus = 'GREEN' THEN 1 ELSE 0 END) [COUNT_BUDGET_GREEN],
        SUM(CASE WHEN PB.strBudgetStatus = 'YELLOW' THEN 1 ELSE 0 END) [COUNT_BUDGET_YELLOW],
        SUM(CASE WHEN PB.strBudgetStatus = 'RED' THEN 1 ELSE 0 END) [COUNT_BUDGET_RED]
	FROM tblProject AS P
		--JOIN for budget statuses
		LEFT JOIN tblProjectBudget as PB
			ON P.intProjectID = PB.int_fk_ProjectID
    WHERE
		(11 IS NULL OR P.intProjectID = 11)

Results: 2 1 0

But if I combine them, I get weird results:

    SELECT 
		SUM(CASE WHEN MS.strMilestoneStatus = 'GREEN' THEN 1 ELSE 0 END) [COUNT_GREEN],
        SUM(CASE WHEN MS.strMilestoneStatus = 'YELLOW' THEN 1 ELSE 0 END) [COUNT_YELLOW],
        SUM(CASE WHEN MS.strMilestoneStatus = 'RED' THEN 1 ELSE 0 END) [COUNT_RED],
		SUM(CASE WHEN PB.strBudgetStatus = 'GREEN' THEN 1 ELSE 0 END) [COUNT_BUDGET_GREEN],
        SUM(CASE WHEN PB.strBudgetStatus = 'YELLOW' THEN 1 ELSE 0 END) [COUNT_BUDGET_YELLOW],
        SUM(CASE WHEN PB.strBudgetStatus = 'RED' THEN 1 ELSE 0 END) [COUNT_BUDGET_RED]
	FROM tblProject AS P
		--JOIN for milestone statuses
		LEFT JOIN tblProjectMilestone as MS
			ON P.intProjectID = MS.int_fk_ProjectID
		--JOIN for budget statuses
		LEFT JOIN tblProjectBudget as PB
			ON P.intProjectID = PB.int_fk_ProjectID
    WHERE
		(11 IS NULL OR P.intProjectID = 11)

Results: 9 12 3 16 8 0

It looks like the first three are multiplied by three, and the next three are multiplied by 8.

Any ideas?

Thanks,
James

 

[bborissov]

That is the LEFT JOIN, It ombines ALL matching records with ALL records, so if you have a table like this:

Table1
  PK
------
  1

Table2
  FK   Something
-----------------
  1       'aaaaaa'
  1       'bbbbbb'
  1       'cccccc'

and if you have:

SELECT *
       from Table1
LEFT JOIN Table2 ON Table1.PK = Table2.FK

you will get a result like this:

PK      FK         Something
-----------------------------
1        1         'aaaaaa'
1        1         'bbbbbb'
1        1         'cccccc'

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[bborissov]

This is NOT tested at all:

If I run this code, I get the proper results:
SELECT COALESCE([COUNT_GREEN], 0)         AS [COUNT_GREEN],
       COALESCE([COUNT_YELLOW], 0)        AS [COUNT_YELLOW],
       COALESCE([COUNT_RED], 0)           AS [COUNT_RED],
       COALESCE([COUNT_BUDGET_GREEN], 0)  AS [COUNT_BUDGET_GREEN],
       COALESCE([COUNT_BUDGET_YELLOW], 0) AS [COUNT_BUDGET_YELLOW],
       COALESCE([COUNT_BUDGET_RED], 0)    AS [COUNT_BUDGET_RED]
FROM (SELECT CASE WHEN 11 IS NULL THEN 0 ELSE P.intProjectID END P.intProjectID,
             SUM(CASE WHEN MS.strMilestoneStatus = 'GREEN' THEN 1 ELSE 0 END) [COUNT_GREEN],
             SUM(CASE WHEN MS.strMilestoneStatus = 'YELLOW' THEN 1 ELSE 0 END) [COUNT_YELLOW],
             SUM(CASE WHEN MS.strMilestoneStatus = 'RED' THEN 1 ELSE 0 END) [COUNT_RED]
     FROM tblProject AS P
          LEFT JOIN tblProjectMilestone as MS  ON P.intProjectID = MS.int_fk_ProjectID
     GROUP BY CASE WHEN 11 IS NULL THEN 0 ELSE P.intProjectID END P.intProjectID
     WHERE (11 IS NULL OR P.intProjectID = 11)) Tbl1
FULL JOIN (SELECT CASE WHEN 11 IS NULL THEN 0 ELSE P.intProjectID END P.intProjectID,
                 SUM(CASE WHEN PB.strBudgetStatus = 'GREEN' THEN 1 ELSE 0 END) [COUNT_BUDGET_GREEN],
                 SUM(CASE WHEN PB.strBudgetStatus = 'YELLOW' THEN 1 ELSE 0 END) [COUNT_BUDGET_YELLOW],
                 SUM(CASE WHEN PB.strBudgetStatus = 'RED' THEN 1 ELSE 0 END) [COUNT_BUDGET_RED]
          FROM tblProject AS P
               LEFT JOIN tblProjectBudget as PB
                    ON P.intProjectID = PB.int_fk_ProjectID
         GROUP BY CASE WHEN 11 IS NULL THEN 0 ELSE P.intProjectID END P.intProjectID
         WHERE (11 IS NULL OR P.intProjectID = 11)) Tbl2
ON Tbl1.intProjectID  = Tbl2.intProjectID

and BTW how 11 could be NULL?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP