CODEWITHSUNDEEP
mysqlsqlselectfilter
Crys Ex
Crys Ex

Reputation: 335

SELECT statement issue with OR

I am trying to do a filter query by using the next statement:

SELECT * FROM user_jobs,users WHERE user_jobs.job_title LIKE "%some_keyword%" **OR** user_jobs.job_title LIKE "%another_keyword%" AND user.id=user_jobs.userid

Specs: users.id is PK and user_jobs.userid is FK to users.id

I am trying to filter the users to get the ones that have similar values as specified. When I run it I get a very long loop and finally a large list of users that contains duplicates. (e.g. I only have 300 users and the query shows over 3000 results)

What am I doing wrong, please?

Thanks in advance!

Upvotes: 0

Views: 76

Answers (4)

Daniel Li
Daniel Li

Reputation: 15389

First off, the AND operator holds precedence in this case. Isolate your logic like so:

SELECT * FROM user_jobs,users WHERE (user_jobs.job_title LIKE "%some_keyword%" OR user_jobs.job_title LIKE "%another_keyword%") AND user.id=user_jobs.userid

Second of all, don't use SELECT * FROM .... This selects all your data, adding network overhead and taking up more time to transfer it all across the server.

Reference: https://dev.mysql.com/doc/refman/5.0/en/func-op-summary-ref.html

Upvotes: 0

Aspirant
Aspirant

Reputation: 2278

Even though you table contains 300 records it will result around 3000 records because you are selecting columns from both the tables but not giving any join condition in your query , so it will CROSS JOIN both the tables.

and for finding the `JOB_TITLE patter you can use Regular Expressions also as

SELECT * FROM USER_JOBS T1,USERS T2 WHERE REGEXP_LIKE(USER_JOBS.JOB_TITLE ,'SOME_KEYWORD|OTHER_KEYWORD') AND T2.ID=T1.USERID;

Upvotes: 0

gcochard
gcochard

Reputation: 11744

You need to use parentheses in that query.

SELECT * FROM user_jobs,users WHERE user.id=user_jobs.userid
AND (user_jobs.job_title LIKE "%some_keyword%"
OR user_jobs.job_title LIKE "%another_keyword%")

Upvotes: 1

user1479055
user1479055

Reputation:

AND takes precedence over OR; use parentheses to achieve the desired result.

SELECT * FROM user_jobs, users
WHERE
    (user_jobs.job_title LIKE "%some_keyword%"
  OR user_jobs.job_title LIKE "%another_keyword%")
AND users.id = user_jobs.userid

Upvotes: 3

Related Questions