What query can give me this result? I have these three tables:

Table1

pro_id(Pk)  pro_name   
1           ABC           
2           def           


Table2

pid pro_id               
1   1                      
2   1                      
3   1                                                 
4   2                                                 
5   2
6   2  


Table3

pro_id  code
1       dc23
1       ef45
2       456F
2       4er


How can I write a query that would give this result?

pro_id    noof pids  noofcodes
1         3          2
2         3          2
The following would do it. SQL Fiddle

WITH T2
     AS (SELECT pro_id,
                COUNT(pid) AS [noof pids]
         FROM   Table2
         GROUP  BY pro_id),
     T3
     AS (SELECT pro_id,
                COUNT(code) noofcodes
         FROM   Table3
         GROUP  BY pro_id)
SELECT T1.pro_id,
       COALESCE([noof pids], 0) AS [noof pids],
       COALESCE(noofcodes, 0)   AS noofcodes
FROM   Table1 T1
       LEFT JOIN T2
         ON T1.pro_id = T2.pro_id
       LEFT JOIN T3
         ON T1.pro_id = T3.pro_id