What query can give me this result?
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