Complex query

Hi Friends!

I have this query which will fetch count of project_ids where proj_CONFIRMED=2 and COUNTRY_ID=6. This is working abs fine. Now I need to add one more to this query. Here is my tables structure.

select count(DISTINCT Pd.Project_id) from project_master PM,PROJ_DETAILS PD where PM.mask=0 and PM.PROJECT_ID=PD.PROJECT_ID and PD.COUNTRY_ID=6 AND PM.proj_CONFIRMED=2

Here is my table design.
PROJECT_MASTER(PROJECT_ID,PROJECT_TITLE,PROJ_CONFI RMED,MASK,REGION_ID,USER_NAME)
PROJ_DETAILS(PROJECT_ID,PROJ_DETAIL,COUNTRY_ID)
COUNTRY_MASTER(COUNTRY_ID,COUNTRY_NAME,REGION_ID)
REGION_MASTER(REGION_ID,REGION_NAME)
USER_MASTER(USER_ID,USER_NAME,COUNTRY_ID)


Apart from country_id=6 I need to see if (PM.USER_NAME'S COUNTRY_ID=6 WHEN REGION_ID=4)

Please Help

Try this. You have to join the country table with the region table. Verify that I spelled it all right!

SELECT R.REGION_NAME, COUNT(DISTINCT Pd.Project_id)
FROM project_master PM, PROJ_DETAILS PD
INNER JOIN COUNTRY_MASTER AS C
ON PD.COUNTRY_ID = C.COUNTRY_ID
INNER JOIN REGION_MASTER AS R
ON C.COUNTRY_ID = R.REGION_ID
WHERE PM.MASK=0
AND PM.PROJECT_ID=PD.PROJECT_ID
AND ( PD.COUNTRY_ID=6 AND AND R.REGION_ID = 4 )
AND PM.PROJ_CONFIRMED=2

Hope this works for you.
Danny