Solved

nesting select statements in MYSQL stored procedure

Posted on 2009-07-01
6
459 Views
Last Modified: 2013-11-23
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

0
Comment
Question by:CameraWithMe
  • 3
  • 3
6 Comments
 
LVL 39

Expert Comment

by:Roger Baklund
ID: 24753226
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
 

Author Comment

by:CameraWithMe
ID: 24753347
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
 
LVL 39

Assisted Solution

by:Roger Baklund
Roger Baklund earned 200 total points
ID: 24753688
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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

Accepted Solution

by:
CameraWithMe earned 0 total points
ID: 24753784
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
 

Author Comment

by:CameraWithMe
ID: 24753890
Reposted in proper category
0
 
LVL 39

Expert Comment

by:Roger Baklund
ID: 24753891
>> 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

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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.
Concerto provides fully managed cloud services and the expertise to provide an easy and reliable route to the cloud. Our best-in-class solutions help you address the toughest IT challenges, find new efficiencies and deliver the best application expe…

948 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

21 Experts available now in Live!

Get 1:1 Help Now