What is wrong with my UNION?
This code
Returns this:
cfFullName tc rintd
Name_1 15 6
Name_2 21 11
Name_3 17 18
Name_4 30 1
Name_5 19 45
Name_6 32 9
Which works as it should.
However, when I perform a Union, no duplicates, a problem develops, here is the UNION:
Returns this:
cfFullName tc rintd
Name_1 15 6
Name_2 21 11
Name_3 17 18
Name_3 17 1
Name_4 30 1
Name_5 19 45
Name_5 19 1
Name_6 32 9
Note the duplicate names at Name_3 & Name_5 & duplicate tc and just a one rintd.
The only change made between the two queries is line 24 on the first query and Line 65 on the second query. My expectations would be no duplicate FullName, no duplicate tc, and the rintd count column would be added, like on Name_5 45 +1 would be 46.
What went wrong?
SELECT
, e.cfFull_Name
, OP.Total_Count tc
, COUNT(DISTINCT ri.Pq_Meeting_Date) rintd
FROM Snival..SFA_Opportunity o
INNER JOIN (
SELECT
opp.Account_Manager_Id
, count(*) as total_count
FROM Snival..SFA_Opportunity opp
WHERE opp.cfWon_Date >= @STARTDATE
AND opp.cfWon_Date <= @ENDDATE -- first day of period to be excluded
GROUP BY
opp.Account_Manager_Id
) as OP ON o.Account_Manager_Id = OP.Account_Manager_Id
INNER JOIN Snival..Employee e ON o.Account_Manager_Id = e.Employee_Id
INNER JOIN Snival..ctLine_of_Business lob ON e.cfLine_of_Business_Id = lob.ctLine_of_Business_Id
INNER JOIN Snival..ctCompany_Code cc ON e.cfCompany_Code_Id = cc.ctCompany_Code_Id
INNER JOIN @TempListCompanyCode tcc ON cc.company_code_name = tcc.CompanyCode
INNER JOIN @TempListLOB tlob ON lob.line_of_business_name = tlob.lob
INNER JOIN Snival..Company co ON o.Company_Id = co.Company_Id
INNER JOIN Snival..Pq_Int_Company ric ON co.Company_Id = ric.Company_Id
INNER JOIN Snival..Pq_Meeting_ri ON ric.Pq_Meetings_Id = ri.Pq_Meetings_Id
INNER JOIN Snival..Email em ON ri.Pq_Meetings_Id = em.Pq_Meetings_Id AND EM.Assigned_To = E.Employee_Id AND EM.Assigned_To = O.Account_Manager_Id
WHERE o.STATUS = 4 -- Won
AND e.Login_id = isnull(@Employee, e.Login_id)
AND o.cfWon_Date >= @STARTDATE
AND o.cfWon_Date <= @ENDDATE -- first day of period to be excluded
AND ri.Pq_Meeting_Date >= Dateadd(dd, -30, dateadd(dd, datediff(dd,0, o.cfWon_Date ), 0) )
AND ri.Pq_Meeting_Completed_Flag = 1
AND ISNULL(o.cfGreen_Completion, 0) = 0
AND ISNULL(o.NO_QUOTE___T_AND_M, '0') IN ('No', '0')
AND ISNULL(o.WARRANTY, 'No') = 'No'
GROUP BY
, e.cfFull_Name
, OP.Total_CountReturns this:
cfFullName tc rintd
Name_1 15 6
Name_2 21 11
Name_3 17 18
Name_4 30 1
Name_5 19 45
Name_6 32 9
Which works as it should.
However, when I perform a Union, no duplicates, a problem develops, here is the UNION:
SELECT
e.cfFull_Name
, COUNT(DISTINCT ri.Pq_Meeting_Date) rintd
, OP.Total_Count tc
FROM Snival..SFA_Opportunity o
INNER JOIN (
SELECT
opp.Account_Manager_Id
, count(*) as total_count
FROM Snival..SFA_Opportunity opp
WHERE opp.cfWon_Date >= @STARTDATE
AND opp.cfWon_Date <= @ENDDATE -- first day of period to be excluded
GROUP BY
opp.Account_Manager_Id
) as OP ON o.Account_Manager_Id = OP.Account_Manager_Id
INNER JOIN Snival..Employee e ON o.Account_Manager_Id = e.Employee_Id
INNER JOIN Snival..ctLine_of_Business lob ON e.cfLine_of_Business_Id = lob.ctLine_of_Business_Id
INNER JOIN Snival..ctCompany_Code cc ON e.cfCompany_Code_Id = cc.ctCompany_Code_Id
INNER JOIN @TempListCompanyCode tcc ON cc.company_code_name = tcc.CompanyCode
INNER JOIN @TempListLOB tlob ON lob.line_of_business_name = tlob.lob
INNER JOIN Snival..Company co ON o.Company_Id = co.Company_Id
INNER JOIN Snival..Pq_Int_Company ric ON co.Company_Id = ric.Company_Id
INNER JOIN Snival..Pq_Meeting_ri ON ric.Pq_Meetings_Id = ri.Pq_Meetings_Id
INNER JOIN Snival..Email em ON ri.Pq_Meetings_Id = em.Pq_Meetings_Id AND EM.Assigned_To = E.Employee_Id AND EM.Assigned_To = O.Account_Manager_Id
WHERE o.STATUS = 4 -- Won
AND e.Login_id = isnull(@Employee, e.Login_id)
AND o.cfWon_Date >= @STARTDATE
AND o.cfWon_Date <= @ENDDATE -- first day of period to be excluded
AND ri.Pq_Meeting_Date >= Dateadd(dd, -30, dateadd(dd, datediff(dd,0, o.cfWon_Date ), 0) )
AND ri.Pq_Meeting_Completed_Flag = 1
AND ISNULL(o.cfGreen_Completion, 0) = 0
AND ISNULL(o.NO_QUOTE___T_AND_M, '0') IN ('No', '0')
AND ISNULL(o.WARRANTY, 'No') = 'No'
GROUP BY
E.cfFull_Name
, OP.Total_Count
UNION
SELECT
e.cfFull_Name
, COUNT(DISTINCT ri.Pq_Meeting_Date) rintd
, OP.Total_Count tc
FROM Snival..SFA_Opportunity o
INNER JOIN (
SELECT
opp.Account_Manager_Id
, count(*) as total_count
FROM Snival..SFA_Opportunity opp
WHERE opp.cfWon_Date >= @STARTDATE
AND opp.cfWon_Date <= @ENDDATE -- first day of period to be excluded
GROUP BY
opp.Account_Manager_Id
) as OP ON o.Account_Manager_Id = OP.Account_Manager_Id
INNER JOIN Snival..Employee e ON o.Account_Manager_Id = e.Employee_Id
INNER JOIN Snival..ctLine_of_Business lob ON e.cfLine_of_Business_Id = lob.ctLine_of_Business_Id
INNER JOIN Snival..ctCompany_Code cc ON e.cfCompany_Code_Id = cc.ctCompany_Code_Id
INNER JOIN @TempListCompanyCode tcc ON cc.company_code_name = tcc.CompanyCode
INNER JOIN @TempListLOB tlob ON lob.line_of_business_name = tlob.lob
INNER JOIN Snival..Company co ON o.Company_Id = co.Company_Id
INNER JOIN Snival..Pq_Int_Company ric ON co.Company_Id = ric.Company_Id
INNER JOIN Snival..Pq_Meeting_ri ON ric.Pq_Meetings_Id = ri.Pq_Meetings_Id
INNER JOIN Snival..Call_ em ON ri.Pq_Meetings_Id = em.Pq_Meetings_Id AND em.Assigned_To = E.Employee_Id AND em.Assigned_To = O.Account_Manager_Id
WHERE o.STATUS = 4 -- Won
AND e.Login_id = isnull(@Employee, e.Login_id)
AND o.cfWon_Date >= @STARTDATE
AND o.cfWon_Date <= @ENDDATE -- first day of period to be excluded
AND ri.Pq_Meeting_Date >= Dateadd(dd, -30, dateadd(dd, datediff(dd,0, o.cfWon_Date ), 0) )
AND ri.Pq_Meeting_Completed_Flag = 1
AND ISNULL(o.cfGreen_Completion, 0) = 0
AND ISNULL(o.NO_QUOTE___T_AND_M, '0') IN ('No', '0')
AND ISNULL(o.WARRANTY, 'No') = 'No'
GROUP BY
E.cfFull_Name
, OP.total_countReturns this:
cfFullName tc rintd
Name_1 15 6
Name_2 21 11
Name_3 17 18
Name_3 17 1
Name_4 30 1
Name_5 19 45
Name_5 19 1
Name_6 32 9
Note the duplicate names at Name_3 & Name_5 & duplicate tc and just a one rintd.
The only change made between the two queries is line 24 on the first query and Line 65 on the second query. My expectations would be no duplicate FullName, no duplicate tc, and the rintd count column would be added, like on Name_5 45 +1 would be 46.
What went wrong?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Be careful, you could be getting the wrong results.
Please try exactly the same query but make this one change to:
UNION ALL
============
also:
Returns this:
cfFullName tc rintd
Name_1 15 6
Name_2 21 11
Name_3 17 18 --<< from Query 1
Name_3 17 1 -- << from Query 2
Name_4 30 1
Name_5 19 45
Name_5 19 1
Name_6 32 9
UNION does make rows unique - but the WHOLE row is evaluated, so those 2 entries for Name_3 are because 18 is different to 1.
But what would happen if both query1 and query2 gave 18 in that third column?
One of those rows would disappear
& if one of those rows disappears the sum would be 18
should the sum be 36?
Please try
UNION ALL
you are at risk of producing the wrong results - just double check
Please try exactly the same query but make this one change to:
UNION ALL
============
also:
Returns this:
cfFullName tc rintd
Name_1 15 6
Name_2 21 11
Name_3 17 18 --<< from Query 1
Name_3 17 1 -- << from Query 2
Name_4 30 1
Name_5 19 45
Name_5 19 1
Name_6 32 9
UNION does make rows unique - but the WHOLE row is evaluated, so those 2 entries for Name_3 are because 18 is different to 1.
But what would happen if both query1 and query2 gave 18 in that third column?
One of those rows would disappear
& if one of those rows disappears the sum would be 18
should the sum be 36?
Please try
UNION ALL
you are at risk of producing the wrong results - just double check
and, what is achieved by this singular difference?
in fact it appears that neither of those tables are referenced in any other place than those joins (not in the where clause, not in the selection clause)
looks to me like you could remove those tables altogether and the results would be the same.
sorry but I suspect your query needs some re-thinking
INNER JOIN Snival..Email em ON ri.Pq_Meetings_Id = em.Pq_Meetings_Id AND EM.Assigned_To = E.Employee_Id AND EM.Assigned_To = O.Account_Manager_Id
INNER JOIN Snival..Call_ em ON ri.Pq_Meetings_Id = em.Pq_Meetings_Id AND em.Assigned_To = E.Employee_Id AND em.Assigned_To = O.Account_Manager_Idin fact it appears that neither of those tables are referenced in any other place than those joins (not in the where clause, not in the selection clause)
looks to me like you could remove those tables altogether and the results would be the same.
sorry but I suspect your query needs some re-thinking
ASKER