Re: [wdvltalk] MySQL Query doing my head in!
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.
Re: [wdvltalk] MySQL Query doing my head in!
Looks great Rudy, I'll try it as soon as I can. Happy Christmas Everyone! 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
Re: [wdvltalk] MySQL Query doing my head in!
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:
Re: [wdvltalk] MySQL Query doing my head in!
matt, did you ever get this resolved? the answer is real easy sorry i did not reply sooner, but for some reason wdvltalk mail often ends up in my spam bucket, and i only go in there once a week (or less) just to double-check the false positives if you ever have sql problems, please feel free to CC: r...@r937.com when you post to the discussion list (assuming the list software allows it) I'd try some LEFT, RIGHT, and INNER Joins and see if you get the results you were hoping for. cheryl, please, that's like handing a child a lighter who is playing with matches and can't get them lit ;o) rudy http://r937.com/ � 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-50259548-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.
Re: [wdvltalk] MySQL Query doing my head in!
Quoting r937 rudy...@rogers.com: matt, did you ever get this resolved? the answer is real easy In a manner of speaking. Basically the query is sound (although if there's a better way of doing it, please let me know!) and there was an issue with the PHP loops behind printing the results. sorry i did not reply sooner, but for some reason wdvltalk mail often ends up in my spam bucket, and i only go in there once a week (or less) just to double-check the false positives No probs if you ever have sql problems, please feel free to CC: r...@r937.com when you post to the discussion list (assuming the list software allows it) We're about to find out... :oP I'd try some LEFT, RIGHT, and INNER Joins and see if you get the results you were hoping for. cheryl, please, that's like handing a child a lighter who is playing with matches and can't get them lit W00T! - Fire! I knew that was the solution to the worlds problems!!! I used to use JOINS and UNIONS when I was doing programming professionally, but that's been nearly 8 years ago now and was MSSQL Server (and, shamed as I am to admit it, MS Access!) - I've forgotten how to use them! :o) M. -- 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-50261940-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.
Re: [wdvltalk] MySQL Query doing my head in!
Can you post a list of the results? Riva On 12/18/2008 8:10 AM, Matthew Macdonald-Wallace wrote: Hi All, Can someone please explain why the following query returns a number of search results for each article instead of a single copy of each article that matches? SELECT DISTINCT resources.resourceId, resourceTitle, extract, iconPath, iconName, resourceTypes.resourceTypeId, resources.resourceTypeId 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 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' ORDER BY resourceTitle DESC Thanks, Matt 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-50161448-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.
Re: [wdvltalk] MySQL Query doing my head in!
Quoting Riva Portman rrp...@roadrunner.com: Can you post a list of the results? Not the exact results, no because it's client-confidential, however the above query effectively generates the following format: 1) Article 1 - published on 18/12/2008 by Me This article deals with waste and the legal issues that surround it 2) Article 1 - published on 18/12/2008 by Me This article deals with waste and the legal issues that surround it 3) Article 2 - published on 18/11/2008 by Me This article deals with the legal sector 4) Article 2 - published on 18/11/2008 by Me This article deals with the legal sector 5) Article 3 - published on 21/11/2008 by Me This article deals with the way in which we deal with waste 6) Article 3 - published on 21/11/2008 by Me This article deals with the way in which we deal with waste and so it continues. What I'm after is: 1) Article 1 - published on 18/12/2008 by Me This article deals with waste and the legal issues that surround it 2) Article 2 - published on 18/11/2008 by Me This article deals with the legal sector 3) Article 3 - published on 21/11/2008 by Me This article deals with the way in which we deal with waste HTH, Matt. -- 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-50162209-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.
Re: [wdvltalk] MySQL Query doing my head in!
I normally work in MS SQL and not MySQL so I could be wrong, but it seems that you should have some joins happening between the tables instead of having this in the Where clause: icons.iconId = resourceTypes.resourceTypeIconId AND resources.resourceTypeId = resourceTypes.resourceTypeId AND categories.categoryId = resource2category.categoryId AND resources.resourceId = resource2category.resourceId I'd try some LEFT, RIGHT, and INNER Joins and see if you get the results you were hoping for. HTH, Cheryl Matthew Macdonald-Wallace matt...@truthisfreedom.org.uk wrote: = Hi All, Can someone please explain why the following query returns a number of search results for each article instead of a single copy of each article that matches? SELECT DISTINCT resources.resourceId, resourceTitle, extract, iconPath, iconName, resourceTypes.resourceTypeId, resources.resourceTypeId 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 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' ORDER BY resourceTitle DESC Thanks, Matt -- 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: crhew...@charter.net To unsubscribe send a blank email to leave-50160786-2349296.9032bcafc912e9043bceba3eb58fe...@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. � 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-50162508-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.
