views:

81

answers:

3

I know it's possible, but I'm not experienced enough to know how to do subqueries.
Here's the situation:

Table 1:
+--------------------+--------------------+
|               v_id |             v_name |
+--------------------+--------------------+
|                  1 |            v_name1 |
+--------------------+--------------------+
| etc...

Table 2:
+--------------------+--------------------+
|               a_id |             a_name |
+--------------------+--------------------+
|                  1 |            a_name1 |
+--------------------+--------------------+
| etc...

Table 3:
+--------------------+--------------------+
|               v_id |               a_id |
+--------------------+--------------------+
|                  1 |                  1 |
+--------------------+--------------------+
|                  1 |                  2 |
+--------------------+--------------------+
|                  1 |                  3 |
+--------------------+--------------------+
|                  2 |                  3 |
+--------------------+--------------------+
|                  2 |                  1 |
+--------------------+--------------------+

I believe this is a quite common situation.
So, I have unique entries in Table 1 and Table 2.
I want to SELECT all rows from Table 1 and get (as the last cell in each row) the number of rows with the corresponding value in Table 3.

This doesn't work:

SELECT t1.* , COUNT(SELECT t3.* FROM  `table_3` t3 WHERE t3.v_id = t1.v_id) as entries
FROM  `table 1` t1;

I'm sure I'm gonna be told off by experts here that it's all wrong, but frankly, that's what I'm looking for (and some helpful solution as well!). ;)

+3  A: 
SELECT t1.* , (SELECT COUNT(*) FROM  `table_3` t3 WHERE t3.v_id = t1.v_id) as t3Count as entries 
FROM  `table 1` t1; 
Rob Farley
the subquery will execute for each row in the outer select, which for large resultsets will affect performance.
davek
Actually, if you look at the plans used in almost every database system these days, you'll see that the correlated subquery will be executed as a join. Doing it this way makes it clearer that working out the value will not add or subtract from the number of rows returned by t1.
Rob Farley
+4  A: 

Use:

   SELECT t1.*,
          COALESCE(x.num_rows, 0) AS entries
     FROM `table 1` t1
LEFT JOIN (SELECT t3.v_id,
                  COUNT(*) 'num_rows'
             FROM `table_3` t3
         GROUP BY t3.v_id) x ON x.v_id = t1.v_id
OMG Ponies
+1 You beat me to it.
Mark Byers
thank you very much! :)
Michal M
+2  A: 
SELECT T1.v_id, COALESCE(COUNT(T3.v_id), 0)
FROM Table1 AS T1
LEFT JOIN Table3 AS T3
ON T1.v_id = T3.v_id
GROUP BY T1.v_id
Mark Byers