I'm trying to figure out how to do what I can only call a recursive query (my knowledge of MySQL is extremely limited). The game I'm designing has a tech tree with items to research that will grant you stronger stuff down the line. The prerequisites are broken down in another table, since each tech item can have 0 or more prereqs.
CREATE TABLE IF NOT EXISTS `tech` (
`id` INT NOT NULL ,
`name` VARCHAR(45) NULL DEFAULT NULL ,
PRIMARY KEY (`id`) );
CREATE TABLE IF NOT EXISTS `prereq` (
`src_id` INT NOT NULL ,
`prereq_id` INT NOT NULL );
INSERT INTO tech (`id`,`name`) VALUES(1001, 'Logic');
INSERT INTO tech (`id`,`name`) VALUES(1002, 'Classical Physics');
INSERT INTO tech (`id`,`name`) VALUES(1005, 'Global Economics');
INSERT INTO tech (`id`,`name`) VALUES(1008, 'Solar Power Collector');
INSERT INTO prereq (`src_id`,`prereq_id`) VALUES (1008,1005);
INSERT INTO prereq (`src_id`,`prereq_id`) VALUES (1005,1001);
INSERT INTO prereq (`src_id`,`prereq_id`) VALUES (1005,1002);
INSERT INTO prereq (`src_id`,`prereq_id`) VALUES (1001,NULL);
INSERT INTO prereq (`src_id`,`prereq_id`) VALUES (1002,NULL);
With this set up, I can query the tables and find that if I want solar power, I need to first research Global Economics:
SELECT t.name, t.id, GROUP_CONCAT(p.prereq_id ORDER BY p.prereq_id ASC)
FROM tech t LEFT JOIN prereq p on t.id=p.src_id
WHERE t.id=1008
GROUP BY t.name, t.id
ORDER BY t.id
What I'm trying to get at is a result set that include the next level of prerequisites, then the next, until we hit nodes that have NULL. So I'm looking for this:
id name
1008 Solar Power Collector
1005 Global Economics
1001 Logic
1002 Classical Physics
Is this even possible within MySQL? I tried to use a cursor, but was unsuccessful. I could handle this within the actual code, but I'd rather it be done server side somehow.
Any help in pointing me in the right direction would be greatly appreciated!