A virtual beer to the man in the corner - worked a treat. Nice touch - a happy customer right before Christmas!!!
Thanks Rudy, M. Quoting r937 <rudy...@rogers.com>: >> 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: matt...@truthisfreedom.org.uk > To unsubscribe send a blank email to > leave-50263304-1955138.08b61207e1c4d2c860eb257fdad37...@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. -- Matthew Macdonald-Wallace matt...@truthisfreedom.org.uk http://www.truthisfreedom.org.uk/ ____ � 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-50266183-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.
