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!). ;)