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.


Roughly:
Begin;
#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;
END;

Open in new window

CameraWithMeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roger BaklundCommented:
You can (and should) do this with joins. Try something like this:
SELECT cc1.Fips_10_4 as cntry, r.rgn, m.city
FROM cc1, camwme.rgn_center as r, masterlist as m
where 
  cc1.Fips_10_4 = r.Fips_10_4 AND 
  cc1.Fips_10_4 = m.Fips_10_4 AND
  r.rng = m.region 

Open in new window

0
CameraWithMeAuthor Commented:
Cxr- thanks for the superfast response.

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.
0
Roger BaklundCommented:
When you say "page" I assume you mean HTML pages. You can not (without considerable effort) create HTML pages from within a stored procedure. You need some scripting/programming language. You post in a Delphi zone, so I assume you are using Delphi for this. :)

If your Deplhi code generates multiple static HTML pages, you could use that single join statement, loop over the results, and write to multiple pages at the same time. See meta code below.

If your Deplhi code is running like a CGI, i.e. it executes for each page click, I would split it in three different SQL statements, similar to those you had in the original question. There is nothing to gain from having this in a one large loop.
currentCountry = false
currentRegion = false
for each country/region/city record:
  if currentCountry != country:
    if currentCountry != false: save currentCountry page
    currentCountry=country
    create/open new country page
  if currentRegion != region:
    if currentRegion != false: save currentRegion page 
    currentRegion = region
    create/open new region page
    write region to country page
  write city to region page

Open in new window

0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

CameraWithMeAuthor Commented:
I thought I posted it in a MYSQL zone. I will see what I can do to recatagorize.
Yes 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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CameraWithMeAuthor Commented:
Reposted in proper category
0
Roger BaklundCommented:
>> I thought I posted it in a MYSQL zone.

You did, this question is in two zones: "Internet and Delphi Programming" and "MySQL Server".

>> I have created views that output the proper html code

SQL views? How did you do that...?

>> What I am trying to do is create the procedure to update these pages once a month.

Using mysql only?

I am sorry for asking so many questions, but I need to understand your situation to be able to help you.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.