nesting select statements in MYSQL stored procedure

I am trying to create a stored procedure that uses the output of one select statement as the input for another. I need to nest these 3 or 4 levels deep, but I cant find anything about this in the manuals.

For example, I want to select a country, then find all the regions within that country then the cities within each region.

I have some specific code to execute at each level to create index pages, so I want to be able to do this as nested loops of some type (foreach, 'while not eof'?) if possible.

The reason that I do not want to use joins here is that i will be creating output to different files at each level.
I will be creating a 'country page' that will link to each city region page and a region page for each country that will link to all the cities within the region.

I need to grab each of the values to create the various strings for each of the outfiles.

I am creating HTML pages. I have created views that output the proper html code, and it works one page at a time. What I am trying to do is create the procedure to update these pages once a month.

(originally posted in wrong category)
#get country
SELECT cc1.Fips_10_4 as cntry FROM cc1;
#for each country get region codes
SELECT  rgn FROM camwme.rgn_center where Fips_10_4 =cntry;
#get cities
SELECT city from masterlist where region=rng and Fips_10_4 =cntry;

Open in new window

Who is Participating?
wcoka2Connect With a Mentor Commented:
for the loop you could do something like this:

declare tempcntry integer;
declare cur1 cursor for
SELECT cc1.Fips_10_4 as cntry FROM cc1;
declare continue handler for not found set done=1;
open cur1;
      fetch cur1 into tempcntry ;
        if done=1 then
          leave loop1;
       end if;
      DECLARE not_found INT DEFAULT 0;
      DECLARE CONTINUE HANDLER FOR 1339 SET not_found=1;
#for each country get region codes
SELECT  rgn FROM camwme.rgn_center where Fips_10_4 =tempcntry;
  end loop loop1;
close cur1;

Open in new window

wcoka2Connect With a Mentor Commented:
To use a value of one select you can store the output in a variable

declare var1 integer;

#get country
SELECT cc1.Fips_10_4 into var1 FROM cc1;
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.