hello,
in a table i store the status of some project (eg. certified = 2 , contracted = 7 , resigned = 1 etc ) now i want to find out count of projects of all the countries with status in (2 , 7). below is my query:

Code:
SELECT count(ps.project_id) as ProjectCnt, isnull(psc.CertificationStatus_ID, 0) , isnull(dbo.uf_parmsel_countryname_of_country(p.MainCountry_ID, 1) , 0) AS Country_VC FROM  dbo.Project_Subprogram_CertificationStatus_T	psc , project_t p , project_subprogram_t ps
 WHERE 
   (psc.Subprogram_ID = '1') 
	AND  (psc.CertificationStatusPerDate_DT <= '2008-01-01')  
	and p.Project_id = psc.Project_id
	and p.Project_id = ps.Project_id
	and p.deleted_dt is null
	and ps.deleted_dt is null
	and ps.resigned_dt is null
	and ps.resigned_dt is null
	and ps.resigned_dt is null
	and p.Maincountry_id is not null
	and (psc.CertificationStatus_ID  in( 2 ,7 ) or psc.CertificationStatus_ID  is null)

	group by   psc.CertificationStatus_ID , p.MainCountry_ID order by maincountry_id
and below is my output:


Code:
ProjectCnt | Status | Country_VC
1	7	GERMANY
6	2	SWITZERLAND
6	7	SWITZERLAND
114	7	TURKEY
218	2	TURKEY
6	2	POLAND
10	7	SOUTH AFRICA
89	2	SOUTH AFRICA
14	2	UNITED STATES
2	7	UNITED STATES
but i want if any of the values in clause is not found then it should return count as 0. in case of the above eg for GERMANY a row should come as :
0 , 2 , GERMANY
also for POLAND:
0 , 7 , POLAND and so on.

how can i get this. i also tried joins but i thing there is somthing to do with in clause..????
Please help

Thanks and brdgs