[ic] Complex/SQL Search help!

[Nathan Young]

Hi Brian.

I've been told that if possible you should replace nested queries with
joins wherever possible.  A join can get all your data into one row, and
it runs much faster than sub selects do.

My understanding of your problem is that you are trying to draw
information about a product from a related zip_zone table where the
product table is related to the zip_zone table by the zone field.

The SQL you gave was:

select * from products where zone = (
                                 SELECT zone from zip_zone
                                 where zip = zip_code
                                 )

As I read it, an equivalent join would be:

select products.* from products, zip_zone where products.zone =
zip_zone.zone and zip_zone.zip = zip_code

Give it a try in a more interactive environment (like the mysql command
line tool) and see if it works.  You'll have to put in a real value for
zip_code of course...

---------------->Nathan




Brian Kosick wrote:
> 
> Hello all,
>         I have been trying to get a search to work all day.  First let me
> explain.  I have a two tables my products table and a zip_zones table with
> two columns zip and zone
> I need to do a search on a user inputted zip to return which should return
> the zone that it's in then do a lookup on the products table to match the
> products in that zone.  The docs say no nested searches allowed, so I have
> to figure out how to do it with SQL
> 
> <psuedo code>
> <input type="text" name="zip_code">
> <input type="mv_sql_query" VALUE="
>                         select * from products where zone = (
>                                 SELECT zone from zip_zone
>                                 where zip = zip_code
>                                 )">
> 
> All I get is an error usually stating "No match for zip_code" or invalid
> search string "zip_code"
> 
> What am I missing?
> 
> 
> Brian Kosick
> Web Programmer
> New Age Consulting Service, Inc.
> 216-619-2000
> briank@nacs.net
> 
> _______________________________________________
> Interchange-users mailing list
> Interchange-users@lists.akopia.com
> http://lists.akopia.com/mailman/listinfo/interchange-users

-- 




=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=


Nathan Young
N. C. Young Design
(707)822-1793
http://ncyoung.com