i have 2 table's.
table TOPICS
topic_id | auto increment
topic_pid | int => points to his parent
topic_name | string
table events
topic_id | int => points to topic table
title | string
i want to get results that show me each How many events each topic and its children have?
for example
TOPIC TABLE
————————————-
topic_id | topic_pid | topic_name
1 0 product
2 1 downloadable
3 2 movie
EVENT TABLE
————————————-
topic_id | title
3 buy
3 like
2 like
my ideal answer:
name | event count product 3 downloadable 3 movie 2
because movie is downloadable's child and downloadable is product's child
my topic child have child