1

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.

Kara
  • 6,115
  • 16
  • 50
  • 57
BS_C3
  • 385
  • 1
  • 6
  • 23
  • 3
    That is why you should always normalize – Gurwinder Singh May 16 '17 at 10:41
  • 1
    Basically you need to normalize `USERS` and then do the LEFT JOIN on the result. Normalizing is covered here: http://stackoverflow.com/questions/17308669/reverse-group-concat-in-mysql – T.J. Crowder May 16 '17 at 11:09
  • Thanks for both your answers. My memories of MySQL are way too rusty and definitely not up to date. Normalization does seem indeed the way to go, which is great! I'm going to play with this concept. – BS_C3 May 16 '17 at 12:04

2 Answers2

0

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
Biber
  • 709
  • 6
  • 19
0

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

Gurwinder Singh
  • 38,557
  • 6
  • 51
  • 76