Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question

2 Answers 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 
share|improve this answer

You can also just use two scalar subqueries:

SELECT pro_id,
      (SELECT COUNT(*) FROM Table2 WHERE pro_id=A.pro_id) [noof pids],
      (SELECT COUNT(*) FROM Table3 WHERE pro_id=A.pro_id) [noofcodes]
  FROM Table1 A;
share|improve this answer
    
Do we need those ISNULLs? I thought COUNT never returned NULL. –  AakashM Apr 4 '13 at 9:58
    
@AakashM You're absolutely right. It didn't even register with me after SQL Server showed the missing 2nd COMPUTE SCALAR in the query plan, because it optimized away the ISNULL. Now it's becoming clear. –  孔夫子 Apr 4 '13 at 10:04

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.