Join 2 tables: one data table and one table of statut and get statut with no entrie

I have this query:

SELECT c.Show_Code, req.Statut_ID, COUNT(req.Statut_ID) 'Count'
FROM [Case] c
JOIN Request req ON req.Case_Number = c.Number
GROUP BY c.Show_Code, req.Statut_ID

The result is:

Show_Code   Statut_ID   Count
564900    开发者_JAVA百科      2         1
568127          2         1

And I have this statut table (Ref_RequestStatut)

ID   Name
1    Test
2    Test2

How can I get this result:

Show_Code   Statut_ID   Count
564900          1         0
564900          2         1
568127          1         0
568127          2         1

I want all the statut, even those which have no value?

Thank

If you are using SQL Server 2005 or later:

WITH counted AS (
  SELECT c.Show_Code, req.Statut_ID, COUNT(req.Statut_ID) 'Count'
  FROM [Case] c
  JOIN Request req ON req.Case_Number = c.Number
  GROUP BY c.Show_Code, req.Statut_ID
),
showcodes AS (
  SELECT DISTINCT Show_Code
  FROM counted
)
SELECT
  s.Show_Code,
  r.ID AS Statut_ID,
  Count = ISNULL(c.Count, 0)
FROM showcodes s
  CROSS JOIN Ref_RequestStatut r
  LEFT JOIN counted c ON s.Show_Code = c.Show_Code AND r.ID = c.Statut_ID
ORDER BY 
  s.Show_Code,
  r.ID