MySQL - Left join ... on ... LIKE

Question

I have 2 tables

On table "Users", each user has a number of skills. They are found on a single column and are concatenated. On table "Skills", each skill has an associated label.

It happens that some users have skills that are not referenced on table "Skills" anymore.

The select I'd like to do should list all records on table users that contain a skill that is not referenced anymore on table skills.

I was trying to do something like:

SELECT user_id 
FROM USERS LEFT JOIN SKILLS 
ON USERS.skills = SKILLS.skill_id 
WHERE SKILLS.skill_id = null

However, the statement ON USERS.skills = SKILLS.skill_id does not fit my needs. The column USERS.skills contains the skill_id concatenated.

I tried to replace that bit by ON USERS.skills LIKE SKILLS.skill_id but it still feels wrong and the query runs forever...

Could you please enlighten me.

Answer 1

You can't do that in this way without programming. There are two possible ways:

1. Read the skills in a text-string and split it and use another statement to read the kills (use the SQL: "WHERE id in (1,2,3)")
2. Use a link-Table. You must have the dependencies in a third table, the table mus have the fields: UserId and SkillId. and if a User have 3 skills the third table must have 3 entries. Than you can select them with a simple SQL-Statement

Answer 2

Ideally you should normalize the schema.

For now, you can use concatenation for using like and length and replace to find the number of items in the comma separated string.

select *
from users u
where length(u.skills) - length(replace(u.skills, ',', '')) + 1 <> (
        select count(*)
        from skills s
        where concat (', ',u.skills,',') like concat ('%, ',s.skill_id,',%')
        );

Demo