Witam. Mam tabele topics:
+----------+----------+------------+--------------+------------+
| topic_id | forum_id | topic_name | topic_author | topic_time |
+----------+----------+------------+--------------+------------+
| 1 | 1 | test56 | 1 | 12333213 |
| 2 | 1 | test | -1 | 1123123 |
| 3 | 1 | arnold | -1 | 1264917749 |
| 4 | 1 | 666 | 1 | 1264917779 |
+----------+----------+------------+--------------+------------+
oraz posts:
+---------+----------+-------------+----------------+------------+
| post_id | topic_id | post_author | post_text | post_time |
+---------+----------+-------------+----------------+------------+
| 2 | 1 | -1 | Najlepsze | 1264355826 |
| 3 | 1 | -1 | tak | 1264356593 |
| 4 | 1 | 1 | poprosze | 1264356612 |
| 5 | 1 | -1 | ytrrrrrrrrrrrr | 1264840083 |
| 6 | 3 | -1 | stefan | 1264917749 |
| 7 | 4 | 1 | jarek | 1264917779 |
+---------+----------+-------------+----------------+------------+
I chciałbym , żeby za pomocą jednego zapytania wyrzucić tytuły tematów oraz ile zawierają wypowiedzi, jednak mi to nie wychodzi. Zapytanie:
select t.topic_name, count(p.post_id) FROM topics t left join posts p ON p.topic_id = t.topic_id WHERE t.forum_id = 1;
Zwraca jedynie:
+------------+------------------+
| topic_name | count(p.post_id) |
+------------+------------------+
| test56 | 6 |
+------------+------------------+
Wie ktoś co robię źle?