I want jobs matching with same skill_ids,location of the job_id parameter skill_id (jobid is parameter)

Question

I have one table jobs with fields

job_id    skill_ids   location
   1       1,2,3        1, 3
   2        2, 3         2
   3        1, 4         4

I want jobs matching with same skill_ids,location of the job_id parameter skill_id (job_id is parameter) skill_ids are comma separated so it should match at-least 2 skills

Answer 1

First of all your tables should follow Normalization

So the tables should be:

Job:

 job_id    job_name
   1         Job One
   2         Job Two
   3         Job Three

Skills:

 skill_id    skill_name
   1         Skill One
   2         Skill Two
   3         Skill Three

Location:

 location_id    location_name
   1         Location One
   2         Location Two

Job_skill

  job_id      skill_id
    1           1
    1           2
    1           3
    2           2
    2           3

Then you can do Mysql Join

  SKILL_ID = 'Your desired SKILL ID';
  SELECT * FROM Job JOIN job_skill on job_skill.job_id = job.job_id WHERE job_skill.skill_id = SKILL_ID;

This is only for JOB and SKILL, you can work a little more to make it work with Locations table:

Job_location

  job_id      location_id
    1           1
    1           3
    2           2
    3           4