How to get number of specific rows from a different table in a subquery
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 |
+------------开发者_如何学JAVA--------+--------------------+
| 1 | 3 |
+--------------------+--------------------+
| 2 | 3 |
+--------------------+--------------------+
| 2 | 1 |
+--------------------+--------------------+
I believe this is a quite common situation.
So, I have unique entries inTable 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!). ;)
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
SELECT t1.* , (SELECT COUNT(*) FROM `table_3` t3 WHERE t3.v_id = t1.v_id) as t3Count as entries
FROM `table 1` t1;
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
精彩评论