CODEWITHSUNDEEP
mysqlsqlsql-serverdatabasepostgresql
Meera M Babu
Meera M Babu

Reputation: 68

Filtering data using multiple AND operators in mysql

I have a table named skills :

screen shot for skills table

I want to search for users having skill java with 2+ year experience and also having skill jquery with 1+ year experience.

I tried this :

SELECT `userid` FROM `skills`
WHERE `skill` =   'java' AND `experience` > 2
  AND `skill` = 'jquery' AND `experience` > 1

The expected result is 15. but the query returned an empty result set.

Any help will be appreciated. Thanks in advance!

Note:

The number of conditions in where clause may vary.

Upvotes: 1

Views: 385

Answers (2)

John Slegers
John Slegers

Reputation: 47091

This is your WHERE-clause :

WHERE `skill` =   'java' AND `experience` > 2
  AND `skill` = 'jquery' AND `experience` > 1

What this means, is that you're selecting elements that must match EACH of the following conditions :

  • skill = 'java'
  • experience > 2
  • skill = 'jquery'
  • experience > 1

This will ALWAYS return the an empty result set, because the value of skill for a single record in the table can never be both equal to 'java' and equal to 'jquery'.

The best way to fix it, is to use a table join :

SELECT S1.userid
 FROM skills As S1
 INNER JOIN skills As S2
     ON S1.userid = S2.userid
 WHERE S1.skill =   'java' AND S1.experience > 2
   AND S2.skill = 'jquery' AND S2.experience > 1

If you want to search for additional skills, just add one join per skill you want to add :

SELECT S1.userid
 FROM skills As S1
 INNER JOIN skills As S2
     ON S1.userid = S2.userid
 INNER JOIN skills As S3
     ON S1.userid = S3.userid
 WHERE S1.skill =   'java' AND S1.experience > 2
   AND S2.skill = 'jquery' AND S2.experience > 1
   AND S3.skill =    'PHP' AND S3.experience > 1

Upvotes: 2

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

Try this:

SELECT userid
FROM skills 
WHERE (skill = 'java' AND experience > 2) 
      OR        
      (skill = 'jquery' AND experience > 1)
GROUP BY userid
HAVING COUNT(DISTINCT skill) = 2

The WHERE clause predicates return users having either of the skills (or both). The HAVING clause makes sure only users having both skills are returned.

Upvotes: 0

Related Questions