user3641053 user3641053 - 5 months ago 18
SQL Question

SQL Server 2008 Query - table join

I'm having trouble getting the results I want from a SQL Server 2008 query. Query 1 produces the results I want for [GeneralErrors] and Query 2 produces the correct results for [RehabErrors] but I really need them both on the same row. When I try and combine the two queries in Query 3, my results for [GeneralErrors] is incorrect but of the way I'm going the tables.

Query1:

SELECT
ReviewID,
SUM(CASE Score_CorrectID_Accuracy WHEN 'Error' THEN 1 ELSE 0 END +
CASE Score_ProperlyIdentified_Accuracy WHEN 'Error' THEN 1 ELSE 0 END) AS GeneralErrors
FROM
Reviews
WHERE
(UserID IS NOT NULL AND UserID <> '')
GROUP BY
Reviews.ReviewID

Results:

ReviewID GeneralErrors
7 0
8 0
9 0
10 0
11 0
12 9
13 0
14 0
15 4


Query 2:

SELECT
Reviews.ReviewID
,COUNT(RehabMetricsCalls.ReviewID) AS RehabErrors
FROM RehabMetrics INNER JOIN
RehabMetricsCalls ON RehabMetrics.RehabMetricID = RehabMetricsCalls.RehabMetricID RIGHT OUTER JOIN
Reviews ON RehabMetricsCalls.ReviewID = Reviews.ReviewID
WHERE
(UserID IS NOT NULL AND UserID <> '')
GROUP BY
Reviews.ReviewID

Results:

ReviewID RehabErrors
7 3
8 0
9 0
10 0
11 0
12 5
13 5
14 0
15 4


Query 3:
My attempt at combining the two queries which produces incorrect results

SELECT DISTINCT
Reviews.ReviewID
,SUM(CASE Score_CorrectID_Accuracy WHEN 'Error' THEN 1 ELSE 0 END +
CASE Score_ProperlyIdentified_Accuracy WHEN 'Error' THEN 1 ELSE 0 END +
CASE Score_MiniMiranda_Accuracy WHEN 'Error' THEN 1 ELSE 0 END +
CASE Score_Tone_Accuracy WHEN 'Error' THEN 1 ELSE 0 END +
CASE Score_Accuracy_Accuracy WHEN 'Error' THEN 1 ELSE 0 END +
CASE Score_Notepad_Accuracy WHEN 'Error' THEN 1 ELSE 0 END +
CASE Score_PCAResponsive_Accuracy WHEN 'Error' THEN 1 ELSE 0 END +
CASE Score_AWGInfo_Accuracy WHEN 'Error' THEN 1 ELSE 0 END +
CASE Complaint_Accuracy WHEN 'Error' THEN 1 ELSE 0 END) AS GeneralErrors
,COUNT(RehabMetricsCalls.ReviewID) AS RehabErrors
FROM RehabMetrics INNER JOIN
RehabMetricsCalls ON RehabMetrics.RehabMetricID = RehabMetricsCalls.RehabMetricID INNER JOIN
Reviews ON RehabMetricsCalls.ReviewID = Reviews.ReviewID
WHERE
(UserID IS NOT NULL AND UserID <> '')
GROUP BY
Reviews.ReviewID


Desired results:

ReviewID GeneralErrors RehabErrors
7 0 3
12 45 5
13 0 5
15 16 4



Answer

do a join..

;with Firstquery  as
(
SELECT
 ReviewID, 
 SUM(CASE Score_CorrectID_Accuracy WHEN 'Error' THEN 1 ELSE 0 END + 
CASE Score_ProperlyIdentified_Accuracy WHEN 'Error' THEN 1 ELSE 0 END) AS GeneralErrors
 FROM  
 Reviews
 WHERE 
(UserID IS NOT NULL AND UserID <> '')
 GROUP BY 
Reviews.ReviewID
)
,Secondquery as
(
SELECT 
  Reviews.ReviewID
 ,COUNT(RehabMetricsCalls.ReviewID) AS RehabErrors
FROM    RehabMetrics INNER JOIN
   RehabMetricsCalls ON RehabMetrics.RehabMetricID = RehabMetricsCalls.RehabMetricID RIGHT OUTER JOIN
 Reviews ON RehabMetricsCalls.ReviewID = Reviews.ReviewID   
 WHERE 
 (UserID IS NOT NULL AND UserID <> '')
 GROUP BY 
  Reviews.ReviewID
)
select 
fs.reviewid,fs.generalerrors,sq.rehaberrors
from
firstquery fs
join
secondquery sq
on fs.reviewid=sq.reviewid