Solved

nesting select statements in MYSQL stored procedure

Posted on 2009-07-01
2
542 Views
Last Modified: 2012-06-27
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)
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

0
Comment
Question by:CameraWithMe
  • 2
2 Comments
 
LVL 2

Assisted Solution

by:wcoka2
wcoka2 earned 500 total points
Comment Utility
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;
0
 
LVL 2

Accepted Solution

by:
wcoka2 earned 500 total points
Comment Utility
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;

  loop1:loop
 

      fetch cur1 into tempcntry ;
 

        if done=1 then

          leave loop1;

       end if;
 

    BEGIN

      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;
 

  end loop loop1;

close cur1;

Open in new window

0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php (http://lmgtfy.com?q=how+easy+is+php)  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now