digger has asked for the wisdom of the Perl Monks concerning the following question:

Esteemed Monks,

I know this is not a SQL forum, but I also know many of you are very knowledgable in the ways of SQL. After googling fruitlessly (mostly because I am not quite sure what to search for) I decided to tap the incredible resources of perlmonks. Of course, the script I am working on is perl, so maybe I'm not completely OT.

I am working with a db that has a list of members and a list of counties they work in.
Members--(one to many)-->Counties

I am writing a little script that searches for members based on the county selected by the user. The syntax for that search is simple, and working. Now I need to go back and look at the counties table and get a list of all the other counties the member works in to display with their member info in my template.

The only method I can see at this point is to execute a SQL statement like SELECT County FROM Counties WHERE ID = $current_id for every row I fetch from my original query, and build the list from the hash returned.

My biggest concern here is performance. Right now, the longest search returns ~100 members, but as the list grows, will this scale, or slow my search to a crawl.

Am I already on the right track, or is there better, more efficient way to do this?

As always, I am very grateful for your help,
digger

P.S. - Platform is Win32, db is Access 2000 using Win32::ODBC interface.

Replies are listed 'Best First'.
Re: Slightly OT - SQL Question
by WhiteBird (Hermit) on Sep 20, 2003 at 20:18 UTC
    Try a google search for SQL +joins and you should find a wealth of information. Since you're using Access 2000, you can always use the query wizard to construct the query you want, and then use the SQL view to grab the exact SQL statement for your perl script. Sometimes it takes some tweaking to get it right so it runs efficiently, but it's a place to start. The syntax of the end result depends on how you have your tables constructed, how you connect your data, and how exactly you want it all to connect in your output.
[reply]
      I did the Goggle thing, but since I wasn't exactly sure what I was looking for, I just kinda floundered. I did read a number of tutorials on the various types of joins, and they didn't seem to fulfill my requirements. It looks like I will have to use 2 separate queries, and then tie the results together using plain old perl.

      Thanks for your input,
      digger
[reply]
Re: Slightly OT - SQL Question
by simonm (Vicar) on Sep 20, 2003 at 20:23 UTC
    To reduce the number of queries you send to the database, along with the associated round-trip delay, you could 'SELECT County FROM Counties WHERE ' . join(' OR ', map "ID = $_", @current_member_ids) . ' order by ID' and then loop over the results building the associations for each member record.

    (Also note that Access may also support an alternative to that OR list using a syntax like "ID is in (7, 12, 42)"; and as a general rule, you should consider using placeholders instead of embedded values in literal queries as shown above.)

    It's true that you can't do it all in one query, for the reasons mentioned in other replies, but doing it in a constant two queries and a foreach loop is definitely better than doing hundreds of separate queries, one for each match.

[reply]
[d/l]
      I din't think about using the "ID is IN (list") syntax. I am giving that a try right now. It will take a little coercion to get the data into usable form, but it should do the trick.

      Thanks much for taking time to respond to an OT question.

      digger
[reply]
A reply falls below the community's threshold of quality. You may see it by logging in.

Back to Seekers of Perl Wisdom