MySQL does not feature recursive queries. In fact, Bill Karwin just wrote a blog post about how MySQL is the only major RDBMS without recursive queries. Notwithstanding, what you need is a Stored Procedure that performs the query as a preorder tree traversal using a queue.
YOUR SAMPLE DATA
mysql> drop database if exists ayman;
Query OK, 2 rows affected (0.03 sec)
mysql> create database ayman;
Query OK, 1 row affected (0.00 sec)
mysql> use ayman
Database changed
mysql> create table topics
-> (id int not null primary key,
-> parent int default null
-> ) engine=MyISAM;
Query OK, 0 rows affected (0.05 sec)
mysql> insert into topics (id) values (1),(5);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> insert into topics (id,parent) values (2,1),(3,2),(4,2);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> create table articles
-> (id int not null,
-> topic int not null,
-> featured int not null
-> ) engine=MyISAM;
Query OK, 0 rows affected (0.13 sec)
mysql> insert into articles values
-> (1,1,1),(2,2,0),(3,3,1),(4,4,1),(5,5,1);
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
Here it is loaded
mysql> select * from articles order by id;
+----+-------+----------+
| id | topic | featured |
+----+-------+----------+
| 1 | 1 | 1 |
| 2 | 2 | 0 |
| 3 | 3 | 1 |
| 4 | 4 | 1 |
| 5 | 5 | 1 |
+----+-------+----------+
5 rows in set (0.00 sec)
mysql> select * from topics order by id;
+----+--------+
| id | parent |
+----+--------+
| 1 | NULL |
| 2 | 1 |
| 3 | 2 |
| 4 | 2 |
| 5 | NULL |
+----+--------+
5 rows in set (0.00 sec)
mysql>
STORED PROCEDURE
DELIMITER $$
DROP FUNCTION IF EXISTS ayman.GetFeaturedArticles $$
CREATE FUNCTION ayman.GetFeaturedArticles (GivenID INT) RETURNS VARCHAR(1024)
DETERMINISTIC
BEGIN
DECLARE rv,rvlist,q,queue,queue_children VARCHAR(1024);
DECLARE queue_length,front_id,pos INT;
SET rv = '';
SET queue = GivenID;
SET queue_length = 1;
WHILE queue_length > 0 DO
SET front_id = FORMAT(queue,0);
IF queue_length = 1 THEN
SET queue = '';
ELSE
SET pos = LOCATE(',',queue) + 1;
SET q = SUBSTR(queue,pos);
SET queue = q;
END IF;
SET queue_length = queue_length - 1;
SELECT IFNULL(qc,'') INTO queue_children FROM
(
SELECT GROUP_CONCAT(AA.id) qc
FROM articles AA INNER JOIN topics BB
ON AA.topic = BB.id
WHERE BB.parent = front_id
) A;
IF LENGTH(queue_children) = 0 THEN
IF LENGTH(queue) = 0 THEN
SET queue_length = 0;
END IF;
ELSE
IF LENGTH(rv) = 0 THEN
SET rv = queue_children;
ELSE
SET rv = CONCAT(rv,',',queue_children);
END IF;
IF LENGTH(queue) = 0 THEN
SET queue = queue_children;
ELSE
SET queue = CONCAT(queue,',',queue_children);
END IF;
SET queue_length = LENGTH(queue) - LENGTH(REPLACE(queue,',','')) + 1;
END IF;
END WHILE;
SET rvlist = CONCAT(GivenID,',',rv);
SET rv = '';
SET @comma = '';
#
# Strip Out Non-Featured Articles
#
SET @searching = 1;
WHILE @searching = 1 DO
SET @searching = (LOCATE(',',rvlist) > 1);
SET front_id = FORMAT(rvlist,0);
SELECT COUNT(1) INTO @ItsFeatured FROM articles WHERE id = front_id AND featured = 1;
IF @ItsFeatured = 1 THEN
SET rv = CONCAT(rv,@comma,front_id);
SET @comma = ',';
END IF;
SET pos = LOCATE(',',rvlist) + 1;
SET q = SUBSTR(rvlist,pos);
SET rvlist = q;
END WHILE;
RETURN rv;
END $$
DELIMITER ;
Here is the execution of it
mysql> select * from articles;
+----+-------+----------+
| id | topic | featured |
+----+-------+----------+
| 1 | 1 | 1 |
| 2 | 2 | 0 |
| 3 | 3 | 1 |
| 4 | 4 | 1 |
| 5 | 5 | 1 |
+----+-------+----------+
5 rows in set (0.00 sec)
mysql> select * from topics;
+----+--------+
| id | parent |
+----+--------+
| 1 | NULL |
| 5 | NULL |
| 2 | 1 |
| 3 | 2 |
| 4 | 2 |
+----+--------+
5 rows in set (0.00 sec)
mysql> select GetFeaturedArticles(1);
+------------------------+
| GetFeaturedArticles(1) |
+------------------------+
| 1,3,4 |
+------------------------+
1 row in set (0.03 sec)
mysql>
I borrowed this code from my earlier post : Find highest level of a hierarchical field: with vs without CTEs
The Stored Procedure first collects all the descendants of a given article's topic hierarchy. Then, it iterates through the return list and strips non-featured articles.
Give it a Try !!!
