Checking 2 Sql Columns And Displaying Result In 1 Column Pt2

I have 2 tables...they are basically the same except for the column name in one of them because they deal with 2 different names, though.. the data I want is in columns that have t

Solution 1:

Have you tried using a UNION clause?

If you are not familiar: http://msdn.microsoft.com/en-us/library/ms180026.aspx

Basically you write two queries returning one of the two columns in question on either side of the UNION and they are returned as one column in the final result set. If the column names are not the same alias them on either side so that they can be combined in the final unioned result set.

Solution 2:

;WITH finding AS (select oa.id, oa.Risk_Rating, COUNT (orf.Prnt_ID) findingcount FROM orderaudits oa LEFT JOIN orderfindings orf on oa.ID = orf.Prnt_ID --WHERE oa.risk_rating > 0 and oa.Investor_Name like '%' + @invest + '%' GROUP BY oa.id, oa.Risk_Rating), Agency AS (select oa.id, oa.Risk_Rating, COUNT (ora.Prnt_ID) agencycount FROM orderaudits oa LEFT JOIN orderagencies ora on oa.ID = ora.Prnt_ID --WHERE oa.risk_rating > 0 and oa.Investor_Name like '%' + @invest + '%' GROUP BY oa.id, oa.Risk_Rating) /* SELECT 'Finding Only', oa.Risk_Rating, COUNT(oa.id) as a, SUM(CASE WHEN f.findingcount > 0 then 1 ELSE 0 END ) as b FROM orderaudits oa LEFT JOIN finding f ON oa.id = f.id LEFT JOIN orderheader oh ON oh.id = oa.orderheader_id WHERE oh.id = @id and oa.risk_rating > 0 and oa.Investor_Name like '%' + @invest + '%' GROUP BY oa.Risk_Rating

UNION ALL

SELECT 'Agency Only', oa.Risk_Rating, COUNT(oa.id) as a, SUM(CASE WHEN a.agencycount > 0 then 1 ELSE 0 END ) as b FROM orderaudits oa LEFT JOIN Agency a ON oa.id = a.id LEFT JOIN orderheader oh ON oh.id = oa.orderheader_id WHERE oh.id = @id and oa.risk_rating > 0 and oa.Investor_Name like '%' + @invest + '%' GROUP BY oa.Risk_Rating

UNION ALL */ --Together SELECT 'aa' + convert(nvarchar, oa.risk_rating) as cat, SUM(CASE WHEN f.findingcount > 0 OR a.agencycount > 0 then 1 ELSE 0 END ) as b FROM orderaudits oa LEFT JOIN finding f ON oa.id = f.id LEFT JOIN Agency a ON oa.id = a.id LEFT JOIN orderheader oh ON oh.id = oa.orderheader_id WHERE oh.id = @id and oa.risk_rating > 0 and oa.Investor_Name like '%' + @invest + '%' GROUP BY oa.Risk_Rating