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.
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;
SELECT city from masterlist where region=rng and Fips_10_4 =cntry;