Need help combining two queries

I have 4 tables of the following structure with the following data:

TableC
__________
c_id
field_C1
field_C2



TableCV
_________
c_id
v_id
detect
start_date


TableV
________
v_id
lang
ext_id



TableCGV
_______
ext_id
field_CGV1
field_CGV2


TableC
_________________________________
c_id      field_C1      field_C2
__________________________________
1      test            test2
2      test4            test8
3      test11            test3


TableV
____________________________
v_id      lang      ext_id
_________________________
ABC      en      4
GHI      en      8
EFG      en      7
KLM      en      11
OPR      en      5





TableCV
___________________________________
c_id      v_id      detect      start_date
___________________________________
1      ABC      0      03/11/2008
1      GHI      0      02/21/2008
1      ABC      1      03/01/2008
1      EFG      0      01/21/2008
2      ABC      1      01/11/2008
2      OPR      1      03/21/2008
2      EFG      1      03/02/2008
3      GHI      1      03/12/2008
3      EFG       1      03/17/2008
4      ABC      0      03/15/2008
4      OPR      1      03/28/2008
4      GHI      0      03/11/2008
4      EFG      0      03/30/2008


TableCGV
____________________________________
ext_id      field_CGV1      field_CGV2
____________________________________
4      test            test
5      test            test
7      test            test
8      test            test
11      test            test

I need to create a stored procedure in which for each v_id I need to retrieve 3 fields:

v_id, count of c_id from TableCV where detect > 0, and count of all c_id from TableCV regardless of value of detect. Also the start_date should be between 03/01/2008 and 03/31/2008


From the sample data above my results should be:

ABC      1      3
EFG      2      3
GHI      1      2
OPR      2      2

SELECT v.v_id, COUNT(DISTINCT(c.c_id)) As Value3
FROM TableC c
INNER JOIN TableCV cv ON c.c_id=cv.c_id
INNER JOIN TableV v ON v.v_id=cv.v_id and v.lang='en'
INNER JOIN TableCGV ON cgv.ext_id=v.ext_id
WHERE cv.start_date BETWEEN '03/01/2008' AND '03/31/2008'
GROUP BY v.v_id


The query above retrieves first and third fields that I need but I have trouble retrieving second field:

ABC      3
EFG      3
GHI      2
OPR      2


And the query below returns first and second value.

 SELECT  v.v_id, COUNT(DISTINCT(c.c_id)) As Value2
FROM TableC c
INNER JOIN TableCV cv ON c.c_id=cv.c_id
INNER JOIN TableV v ON v.v_id=cv.v_id and v.lang='en'
INNER JOIN TableCGV ON cgv.ext_id=v.ext_id
WHERE cv.start_date BETWEEN '03/01/2008' AND '03/31/2008'
GROUP BY v.v_id AND cv.detect > '0'


I have trouble consructing a query that would return all 3 values in the same resultset.

Please helpStart Free Trial