question

Salik avatar image
Salik asked

Getting Record Count From two Table join.

I have SQL Server 2000, Below are table and required result is also define, please help me to get the result

                    
TBL_Question_Choice                    
-------------------------------------   	                    
Choice_ID    | Question_ID|	Choice                    
-------------------------------------                    
1   |   1	  |        Karachi                    
2   |   1	  |        Lahore                    
3   |   1	  |       Islamabad                    
4   |   2	  |         Yes                    
5   |   2	  |          No                    
                    
TBL_Transaction                    
-----------------------------                       
Transaction_ID| Choice_ID                    
-----------------------------                    
1   |              1                    
2    |             1                    
3   |              3                    
4    |             2                    
5     |            2                    
6      |           5                    
7       |          5                    
8      |           5                    
9       |          3                    
                    
Required Result 					                    
Question_ID |	Choice_ID	|	Choice	|	Count(Choice_ID)                    
1   |	1	|	Karachi	|	2                    
1   |	2	|	Lahore	|	2                    
1   |	3	|	Islamabad	|	2                    
2   |	4	|	Yes	|	0                    
2   |	5	|	No	|	3                    
sql-server-2000query
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Kristen avatar image
Kristen answered
            
CREATE TABLE #TBL_Question_Choice            
(            
    ChoiceID int,            
    Question_ID int,            
    Choice varchar(50)            
)            
            
CREATE TABLE #TBL_Transaction            
(            
    Transaction_ID int,            
    ChoiceID int            
)            
GO            
INSERT INTO #TBL_Question_Choice            
SELECT 1   ,   1  ,        'Karachi' UNION ALL            
SELECT 2   ,   1  ,        'Lahore' UNION ALL            
SELECT 3   ,   1  ,        'Islamabad' UNION ALL            
SELECT 4   ,   2  ,        'Yes' UNION ALL            
SELECT 5   ,   2  ,        'No'            
            
INSERT INTO #TBL_Transaction            
SELECT 1   ,              1 UNION ALL            
SELECT 2    ,             1 UNION ALL            
SELECT 3   ,              3 UNION ALL            
SELECT 4    ,             2 UNION ALL            
SELECT 5     ,            2 UNION ALL            
SELECT 6      ,           5 UNION ALL            
SELECT 7       ,          5 UNION ALL            
SELECT 8      ,           5 UNION ALL            
SELECT 9       ,          3            
GO            
            
SELECT  QC.Question_ID,            
    QC.ChoiceID,            
    QC.Choice,            
    [COUNT] = SUM(CASE WHEN T.ChoiceID IS NULL THEN 0 ELSE 1 END)	-- Avoid warning on NULLS            
FROM    #TBL_Question_Choice AS QC            
    LEFT OUTER JOIN #TBL_Transaction AS T            
    	ON T.ChoiceID = QC.ChoiceID            
GROUP BY QC.Question_ID,            
    QC.ChoiceID,            
    QC.Choice            
ORDER BY QC.Question_ID,            
    QC.ChoiceID            
GO            
DROP TABLE #TBL_Transaction            
DROP TABLE #TBL_Question_Choice            
GO            
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.