SQL long query issue

Question

I'm currently working on the very last aspect of my project and am looking to build a matching function that is dynamic. Once I get this query to work I will have it solved, problem is its returning the wrong values

SELECT `Advert_ID`, `User_ID`, `Username` FROM adverts 
WHERE (     `Skill_Proffession_Needed` LIKE 'Plumbing' 
        AND `Skill_Area_Needed` LIKE 'Ceptic' 
        AND `Skill_Level_Needed` LIKE 'Proffessional')
OR (`Skill_Proffession_Needed` LIKE 'Plumbing' 
        AND `Skill_Area_Needed` LIKE 'Heating' 
        AND `Skill_Level_Needed` LIKE 'Proffessional') 
OR (`Skill_Proffession_Needed` LIKE 'Plumbing' 
        AND `Skill_Area_Needed` LIKE 'Sink' 
        AND `Skill_Level_Needed` LIKE 'Proffessional')
AND `User_ID` = '16' 
AND `Location_Country` LIKE 'Ireland' 
AND `Location_City` LIKE 'Dublin' AND Active = 1

Problem is it seems to be ignoring the last 4 AND statements and returning Inactive Adverts and Adverts not in that location. I have tried rearranging the last 4 AND statements and placing them at the top, and still the same error. I also have tried adding extra brackets into the OR queries to prioritize and still had the same issue.

Any help at all would be very helpful

score 3 · Accepted Answer · answered Apr 03 '14 at 22:21

You need to organize your query to have one group all OR's like WHERE (all or operations) AND operations

SELECT `Advert_ID`, `User_ID`, `Username` FROM adverts 
WHERE (
   (`Skill_Proffession_Needed` LIKE 'Plumbing' AND `Skill_Area_Needed` LIKE 'Ceptic' AND `Skill_Level_Needed` LIKE 'Proffessional')
OR (`Skill_Proffession_Needed` LIKE 'Plumbing' AND `Skill_Area_Needed` LIKE 'Heating' AND `Skill_Level_Needed` LIKE 'Proffessional') 
OR (`Skill_Proffession_Needed` LIKE 'Plumbing' AND `Skill_Area_Needed` LIKE 'Sink' AND `Skill_Level_Needed` LIKE 'Proffessional')
)
AND `User_ID` = '16' 
AND `Location_Country` LIKE 'Ireland' 
AND `Location_City` LIKE 'Dublin'
AND Active = 1

M Khalid Junaid Thank you so much! That worked as expected and Ive tested it multiple ways. I cant thank you enough! :D — IrishFeeney92, Apr 03 '14 at 22:38

score 1 · Answer 2 · edited May 23 '17 at 12:20

I'm guessing the order of operations for 'and' and 'or' is the problem: SQL Logic Operator Precedence: And and Or

try it like this:

SELECT `Advert_ID`
    ,`User_ID`
    ,`Username`
FROM adverts
WHERE ((
        `Skill_Proffession_Needed` LIKE 'Plumbing'
        AND `Skill_Area_Needed` LIKE 'Ceptic'
        AND `Skill_Level_Needed` LIKE 'Proffessional'
        )
    OR (
        `Skill_Proffession_Needed` LIKE 'Plumbing'
        AND `Skill_Area_Needed` LIKE 'Heating'
        AND `Skill_Level_Needed` LIKE 'Proffessional'
        )
    OR (
        `Skill_Proffession_Needed` LIKE 'Plumbing'
        AND `Skill_Area_Needed` LIKE 'Sink'
        AND `Skill_Level_Needed` LIKE 'Proffessional'
        ))
    AND `User_ID` = '16'
    AND `Location_Country` LIKE 'Ireland'
    AND `Location_City` LIKE 'Dublin'
    AND Active = 1

score 0 · Answer 3 · answered Apr 03 '14 at 22:24

0

That is because your two outer OR conditions is triggered. By the way, you should avoid using LIKE if you do not need to as it affects how indexes is accessed.

EDIT: M Khalid Junaid described in a better way my point.

answered Apr 03 '14 at 22:24

Vercingetorix

101
1
8

SQL long query issue

3 Answers3