Basically the query is sound ...
sorry, no, not quite
here's what i saw...
WHERE icons.iconId = resourceTypes.resourceTypeIconId
AND resources.resourceTypeId = resourceTypes.resourceTypeId
AND categories.categoryId = resource2category.categoryId
AND resources.resourceId = resource2category.resourceId
AND resourceTitle LIKE "%legal%"
OR keywords LIKE "%legal%"
OR extract LIKE "%legal%"
OR resourceTitle LIKE "%waste%"
OR keywords LIKE "%waste%"
OR extract LIKE "%waste%"
AND resourceIsActive = '1'
the problem is that ANDs take precedence over ORs, and you neglected to use
parentheses to delineate your logical conditions
the above WHERE clause will be executed ~as though~ it had parentheses, like
this --
WHERE (
icons.iconId = resourceTypes.resourceTypeIconId
AND resources.resourceTypeId = resourceTypes.resourceTypeId
AND categories.categoryId = resource2category.categoryId
AND resources.resourceId = resource2category.resourceId
AND resourceTitle LIKE "%legal%"
)
OR keywords LIKE "%legal%"
OR extract LIKE "%legal%"
OR resourceTitle LIKE "%waste%"
OR keywords LIKE "%waste%"
OR (
extract LIKE "%waste%"
AND resourceIsActive = '1'
)
notice that the join conditions are all clustered up there with the first
LIKE
the other LIKEs are by themselves
this means that if, for example, the extract column contains the search
string, then that row is returned, JOINED TO **ALL** ROWS FROM THE OTHER
TABLES, completely disregarding the proper join conditions between tables --
which apply only to the LIKE for the resourceTitle column
you can see the disastrous effect this has by simply removing the DISTINCT
keyword from the SELECT, which i'm sure you tossed in there just to reduce
the amount of crap being returned -- icons with unrelated resources,
resources with unrelated resource types, resource types with unrelated
categories, resources which aren't active, and so on
there are two solutions
1. parenthesize your conditions properly
WHERE icons.iconId = resourceTypes.resourceTypeIconId
AND resources.resourceTypeId = resourceTypes.resourceTypeId
AND categories.categoryId = resource2category.categoryId
AND resources.resourceId = resource2category.resourceId
AND (
resourceTitle LIKE "%legal%"
OR keywords LIKE "%legal%"
OR extract LIKE "%legal%"
OR resourceTitle LIKE "%waste%"
OR keywords LIKE "%waste%"
OR extract LIKE "%waste%"
)
AND resourceIsActive = '1'
2. (preferred) use JOIN syntax
cheryl was trying to say the same thing (although it didn't come across too
well)
change this --
FROM resources,
icons,
resourceTypes,
categories,
resource2category
WHERE icons.iconId = resourceTypes.resourceTypeIconId
AND resources.resourceTypeId = resourceTypes.resourceTypeId
AND categories.categoryId = resource2category.categoryId
AND resources.resourceId = resource2category.resourceId
to this --
FROM resources
INNER
JOIN resourceTypes
ON resourceTypes.resourceTypeId = resources.resourceTypeId
INNER
JOIN icons
ON icons.iconId = resourceTypes.resourceTypeIconId
INNER
JOIN resource2category
ON resource2category.resourceId = resources.resourceId
INNER
JOIN categories
ON categories.categoryId = resource2category.categoryId
then your WHERE clause simplifies to --
WHERE resourceIsActive = '1'
AND (
resourceTitle LIKE "%legal%"
OR keywords LIKE "%legal%"
OR extract LIKE "%legal%"
OR resourceTitle LIKE "%waste%"
OR keywords LIKE "%waste%"
OR extract LIKE "%waste%"
)
note that you still need parentheses
try that without DISTINCT and watch the correct results roll in -- and much
more quickly, too
;o)
____ � The WDVL Discussion List from WDVL.COM � ____
To Join wdvltalk, Send An Email To:
mailto:subscribe-wdvlt...@intm-dl.sparklist.com or
use the web interface http://e-newsletters.internet.com/discussionlists.html/
Send Your Posts To: wdvltalk@lists.wdvl.com
To change subscription settings, add a password or view the web interface:
http://intm-dl.sparklist.com/read/?forum=wdvltalk
________________ http://www.wdvl.com _______________________
You are currently subscribed to wdvltalk as: arch...@jab.org
To unsubscribe send a blank email to
leave-50263304-230552.f22dcf5120bad5a91a87f69f1e923...@lists.wdvl.com
To unsubscribe via postal mail, please contact us at:
Jupitermedia Corp.
Attn: Discussion List Management
475 Park Avenue South
New York, NY 10016
Please include the email address which you have been contacted with.
