I have got table "one to many" with structure "id, user_id, skill". User could have multiple skills, for example "mysql" and "php".
When somebody would like to search user with skills "php" and "mysql" should write in search-form "php mysql".
I made some MYSQL query(condition): "... WHERE skill LIKE '%php%' AND skill LIKE '%mysql%'" but it returns nothing.
How can I improve that?
Also I tried something like "WHERE CONCAT_WS(' ', skill) LIKE '%php%' AND CONCAT_WS(' ', skill) LIKE '%mysql%'". It's also return nothing.
Thanks for your help!
Use conditional aggregation:
SELECT t1.id, t1.name
FROM users t1
INNER JOIN
(
SELECT s.user_id
FROM skills s
GROUP BY s.user_id
HAVING SUM(CASE WHEN s.skill LIKE '%php%' OR
s.skill LIKE '%mysql%' THEN 1 ELSE 0 END) >= 2
) t2
ON t2.user_id = t1.id
The inner query finds users who have both the PHP and MySQL skill, and this result is then used to filter the users table to return only the names who match this requirement.
I assume in my query that a given skill will appear once and only once for a given user. If the same skill could appear multiple times for the same user, the query will need to be slightly modified.
Update:
If your skills table might have the same skill listed for a given user multiple times, you can ignore these duplicate entries by using DISTINCT:
SELECT t1.id, t1.name
FROM users t1
INNER JOIN
(
SELECT s.user_id
FROM (SELECT DISTINCT user_id, skill FROM skills) s
GROUP BY s.user_id
HAVING SUM(CASE WHEN s.skill LIKE '%php%' OR s.skill LIKE '%mysql%'
OR s.skill LIKE '%javascript%' THEN 1 ELSE 0 END) >= 3
) t2
ON t2.user_id = t1.id
Note in the Fiddle below that Mark does not appear in the result set, as although he has PHP as a skill listed twice, he does not have either MySQL or JavaScript.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments