Ridd Ridd - 3 months ago 6
SQL Question

Mysql - search in "one to many" table

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
    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 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.