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%'"
"WHERE CONCAT_WS(' ', skill) LIKE '%php%' AND CONCAT_WS(' ', skill) LIKE '%mysql%'"
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 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
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.