Welcome to the MacNN Forums.

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

You are here: MacNN Forums > Software - Troubleshooting and Discussion > Developer Center > SQL where clause priority

SQL where clause priority
Thread Tools
madmacgames
Grizzled Veteran
Join Date: Oct 2003
Status: Offline
Reply With Quote
Jul 6, 2005, 03:15 PM
 
I've hit a bit of a stump. Is there a way to force a where clause priority? What I mean is, when evaluating the clause, if matching on the first section, then not bothering to match on the other sections.

I could do this with separate queries, but it would require about 7 separate queries, which seems kind of inefficient to me.

I am needing this for taxes, shipping, etc, and here is an example for taxes:
[php]
"SELECT
SUM(tTV.taxPercentage) AS taxPercentage
FROM
tTaxValue tTV,
tGeoRegion tGR,
tGeoRegionZone tGRZ
WHERE
tTV.geoRegionUID = tGR.geoRegionUID AND
tTV.taxCategoryUID = " . (int)$taxCategoryUID . " AND
tGRZ.geoRegionUID = tGR.geoRegionUID AND
(
(tGRZ.countryUID = " . (int)$countryUID . " AND
tGRZ.zoneUID = " . (int)$zoneUID . " AND
lower(tGRZ.city) = '" . strtolower($city) . "' AND
lower(tGRZ.postCode) = '" . strtolower($postCode) . "')
OR
(tGRZ.countryUID = " . (int)$countryUID . " AND
tGRZ.zoneUID = " . (int)$zoneUID . " AND
lower(tGRZ.city) = '" . strtolower($city) . "' AND
tGRZ.postCode IS NULL)
OR
(tGRZ.countryUID = " . (int)$countryUID . " AND
tGRZ.zoneUID = " . (int)$zoneUID . " AND
tGRZ.city IS NULL AND
lower(tGRZ.postCode) = '" . strtolower($postCode) . "')
OR
(tGRZ.countryUID = " . (int)$countryUID . " AND
tGRZ.zoneUID = " . (int)$zoneUID . " AND
tGRZ.city IS NULL AND
tGRZ.postCode IS NULL)
OR
(tGRZ.countryUID = " . (int)$countryUID . " AND
tGRZ.zoneUID IS NULL AND
tGRZ.city IS NULL AND
lower(tGRZ.postCode) = '" . strtolower($postCode) . "')
OR
(tGRZ.countryUID = " . (int)$countryUID . " AND
tGRZ.zoneUID IS NULL AND
tGRZ.city IS NULL AND
tGRZ.postCode IS NULL)
OR
(tGRZ.countryUID IS NULL AND
tGRZ.zoneUID IS NULL AND
tGRZ.city IS NULL AND
tGRZ.postCode IS NULL)
)
LIMIT 1";
[/php]

A bit of background:

Basically, the world can be split up into several "Geo Regions", and each Geo Region can consist of an unlimited number of "Geo Region Zones" that can be combinations of countries, country zones (states, provinces, etc), cities, and postal codes. An Example, a Geo Region might be the EU, and its Zones would be all of the EU countries. Or a Geo Region might be just Los Angeles containing a single Zone that would be "USA, California, Los Angeles".

A hierarchy must be followed for a Geo Region's Zones, ie, if for a Geo Region Zone you set a Country Zone, you must also set the country. And if you set a city, you must also set a Country Zone and a Country. The exception is postal codes, which do not always specify a distinct zone like a state or city does (example United States postal codes are a lateral system based on roads). The only thing necessary when supplying a postal code to be a Geo Region's Zone is to also give the country.

Zones cannot overlap on the same level, but they can overlap on different levels (ie, one Geo Region distinctly inside another Geo Region). As an example, one Geo Region cannot contain the entire United States if another already contains the entire United States. However, one may contain the entire United States, and another may contain California, and another may contain the city of Los Angeles.

So that leads to the SQL, where I want to start and match on the most minute Geo Region (down to the city and post code), and from there get broader and broader (ending on a Geo Region of the entire world if it exists), until a match is found.

Now, the problem is that the SQL WHERE clause priority is not honored. Does anybody know of a method to force it to match in the order I specify??

As it is now, for example if I have a Geo Region for the United States, and one for California, and one for Los Angeles, the SQL will match on any of those if the address is in Los Angeles, when I only want to get the results for Los Angeles (the 2nd WHERE mini clause).
The only thing necessary for evil to flourish is for good men to do nothing
- Edmund Burke
     
   
Thread Tools
 
Forum Links
Forum Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Top
Privacy Policy
All times are GMT -4. The time now is 05:59 PM.
All contents of these forums © 1995-2017 MacNN. All rights reserved.
Branding + Design: www.gesamtbild.com
vBulletin v.3.8.8 © 2000-2017, Jelsoft Enterprises Ltd.,