We have two different queries:
(1) Master query:
SELECT forum_id, forum_name, parent_id, left_id, right_id
WHERE parent_id =0
ORDER BY forum_name
This query returns 187 records.
We have transaction query based on output value of "left_id" and "right_id".
(2) Transaction query is:
SELECT forum_name, count( * ) AS cat_count, SUM( forum_posts ) AS post_count, SUM( forum_topics ) AS topic_count
WHERE `left_id` >= "747"
AND `right_id` <= "900"
for this we have to execute transaction query 187 times to get all forums records.
We need to write a procedure or query to get all records at once.
How to write a procedure for this wit use of cursor to save master query result in cursor and pass it in procedure to return all records set.