1

I'm trying to running this query:

SELECT * 
FROM 
    (`default_users`) 
    LEFT JOIN `default_profiles` ON `default_profiles`.`user_id` = `default_users`.`id` 
WHERE 
    `default_users`.`group_id` = '3' 
    OR `default_users`.`group_id` = '4' 
    OR `default_users`.`group_id` = '5' 
    AND `default_users`.`email` LIKE '%polar%' 
    OR `default_users`.`username` LIKE '%polar%' 
ORDER BY `default_users`.`id` DESC

But get unwanted results and don't know the cause and by unwanted results means webmaster which I think isn like %polar%. Here are some sample data for testing http://pastebin.com/Fyx0TcGY, any advice?

1
  • 5
    Probably an order of operations problem... add brackets as necessary in the WHERE clause.
    – Jon Seigel
    Aug 8 '12 at 16:22
1

I optimized your query. I use IN instead OR because your point to the same field default_users.group_id and I added the brackets after the AND with (default_users.email LIKE '%polar%' OR default_users.username LIKE '%polar%'). You can use || instead OR (works the same).

SELECT 
    default_users.*,default_profiles.* 
FROM test.default_users 
LEFT JOIN default_profiles  ON (default_profiles.user_id = default_users.id)
WHERE default_users.group_id IN (3,4,5)
    AND (default_users.email LIKE '%polar%' 
    OR default_users.username LIKE '%polar%')
ORDER BY default_users.id DESC;
2

Yes @jon-seigel you're right and thanks for your suggestions didn't see it before :( I add some parentheses and now it works see the right query below:

SELECT * FROM (default_users) LEFT JOIN default_profiles ON
default_profiles.user_id = default_users.id WHERE (default_users.group_id = '3' OR default_users.group_id = '4' OR default_users.group_id = '5') AND (default_users.email LIKE '%polar%' OR default_users.username LIKE '%polar%') ORDER BY default_users.id DESC

1
  • Otherwise, is group_id a string type?
    – dezso
    Aug 8 '12 at 16:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.