1

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.

Community
  • 1
  • 1
Meera M Babu
  • 68
  • 6

2 Answers2

2

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
John Slegers
  • 45,213
  • 22
  • 199
  • 169
  • What to do if i have more skills to search with? I think each time I have to add an inner join for each search criteria. – Meera M Babu Feb 10 '16 at 11:51
  • @MeeraMBabu : You can use as many joins as you want. Check the update at the bottom of my answer – John Slegers Feb 10 '16 at 11:54
  • please edit your answer for selecting distinct S1.userid. – Meera M Babu Feb 10 '16 at 12:57
  • @MeeraMBabu : What do you mean? All values returned by your query should already be distinct, assuming that every user can not have the same skill more than once. – John Slegers Feb 10 '16 at 13:06
  • If i have to search with 'OR' ie. For candidates having experience in java or jquery, userid is repeating.In that case,i think distinct must be used. Am I right? – Meera M Babu Feb 11 '16 at 04:14
  • What to do if i have to search for user that dont have experience in a particular skill? – Meera M Babu Feb 11 '16 at 09:31
  • @MeeraMBabu : Assuming that you have a user table with a field named id, you should be able to do something like `SELECT id As UserId FROM users LEFT JOIN skills ON skills.userid = users.id WHERE skills.userid IS NULL`, to get all user ids of users without any skills in the skill table. See http://stackoverflow.com/questions/5886095/whats-the-best-way-to-use-left-outer-join-to-check-for-non-existence-of-related – John Slegers Feb 11 '16 at 14:04
  • @JohnSlegars :I got that by joining with the user table.Thank you :) – Meera M Babu Feb 12 '16 at 11:00
0

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.

Giorgos Betsos
  • 71,379
  • 9
  • 63
  • 98