Grouping and Returning Zero if no records are found

I know there are various threads out there about this topic.

I am trying to create a query that returns a predefined groping of records and if there are not any results found I would like to return Zero. Most of the threads suggest we create a temporary table to house this grouping data but i will need to do it in a single statement. However we can make use of sub queries.

This was my original code...

and it returns the correct values but does not contain the groupings that do not have tickets:

SELECT SC.Service_Company_Code
     , SSPM.Mapping
     , COUNT(T.Service_Ticket_Id) AS 'Ticket_Count'
FROM SV_Service_Ticket T
 INNER JOIN KS_SedonaSync_Problem SSP ON T.Sub_Problem_Id = SSP.Problem_Id
  RIGHT JOIN KS_SedonaSync_Problem_Sub_Mapping SSPM ON SSP.SedonaSync_Problem_Sub_Mapping_Id = SSPM.SedonaSync_Problem_Sub_Mapping_Id 
 RIGHT JOIN SV_Service_Company SC ON T.Service_Company_Id = SC.Service_Company_Id
WHERE SC.Vendor_Id = 1
 AND SC.Inactive = 'N'
 AND SC.Service_Company_Id <> 1
 AND SSPM.Inactive = 'N'
 AND T.Ticket_Status <> 'CL'
GROUP BY SC.Service_Company_Code, SSPM.Mapping
ORDER BY SC.Service_Company_Code, SSPM.Mapping

My revised code...

now contains a sub query that does show the correct groupings and ran alone without linking in the Ticket table. But again when linked to the ticket table it does not show the grouping when there are no records present. The use of the left join is supposed to show me null records when they do not exist.

SELECT Q.Service_Company_Code
     , Q.Mapping
     , COUNT(T.Service_Ticket_Id) AS 'Ticket_Count'
FROM (SELECT SC.Service_Company_Id, SC.Service_Company_Code, SSP.Problem_Id, SSPM.SedonaSync_Problem_Sub_Mapping_Id, SSPM.Mapping
      FROM SV_Service_Company SC
       FULL OUTER JOIN KS_SedonaSync_Problem_Sub_Mapping SSPM ON 1=1
        LEFT JOIN KS_SedonaSync_Problem SSP ON SSPM.SedonaSync_Problem_Sub_Mapping_Id = SSP.SedonaSync_Problem_Sub_Mapping_Id
      WHERE SC.Vendor_Id = 1
       AND SC.Inactive = 'N'
       AND SC.Service_Company_Id <> 1
       AND SSPM.Inactive = 'N'
      ) Q
 LEFT JOIN SV_Service_Ticket T ON T.Sub_Problem_Id = Q.Problem_Id AND T.Service_Company_Id = Q.Service_Company_Id
WHERE T.Ticket_Status <> 'CL'
GROUP BY Q.Service_Company_Code, Q.Mapping 
ORDER BY Q.Service_Company_Code, Q.Mapping 

Any help is greatly welcomed, Thank you in advance.

Brad Swindell

EDIT: This is the current result set.

This is the desired result set.

EDIT: I have found a working solution thanks to Mike M!

SELECT SC.Service_Company_Code
     , SSPM.Mapping
     , COUNT(T.Service_Ticket_Id) AS 'Ticket_Count'
FROM SV_Service_Company SC
  CROSS JOIN KS_SedonaSync_Problem SSP
  INNER JOIN KS_SedonaSync_Problem_Sub_Mapping SSPM ON SSP.SedonaSync_Problem_Sub_Mapping_Id = SSPM.SedonaSync_Problem_Sub_Mapping_Id
   AND SC.Vendor_Id = 1
   AND SC.Inactive = 'N'
   AND SC.Service_Company_Id <> 1 
  LEFT JOIN SV_Service_Ticket T ON T.Sub_Problem_Id = SSP.Problem_Id
   AND T.Service_Company_Id = SC.Service_Company_Id
   AND T.Ticket_Status <> 'CL'
WHERE SSPM.Inactive = 'N'
GROUP BY SC.Service_Company_Code, SSPM.Mapping
ORDER BY SC.Service_Company_Code, SSPM.Mapping