Query for matching comma seperated values from two tables

I have two tables. Student and Job. I need to search students who possess all the skills required by job.

    Student                      Job
StudentId MandSkills           JobPostId    MandSkills
  208        2,16,17              36         2,16,18
  209        2,16       
  210        2,18,34        
  211        2,16,17        
  212        2,17,16,23     
  213        2,16,17        
  214        2,16,17        
  215        2,18,17,28     
  217        2,16,17

I have written a query

SELECT 
   S.StudentId, S.MandSkills, JP.items 
FROM 
   Split((SELECT MandSkills FROM JobPosts WHERE JobPostId = 36),',') JP, 
   Students S   
WHERE 
    JP.items IN (SELECT items FROM Split(S.MandSkills,','))

[Split() gets comma separated string values as input and returns separate values in table form]

which returns a result like this:

studentId MandSkills items
    208 2,16,17 2
    209 2,16    2
    210 2   2
    211 2,16,17 2
    212 2,16,17 2
    213 2,16,17 2
    214 2,16,17 2
    215 2,16,17 2
    217 2,16,17 2
    218 2,16,17,26  2
    219 2,16    2
    221 2,16    2
    208 2,16,17 16
    209 2,16    16
    211 2,16,17 16
    212 2,16,17 16
    213 2,16,17 16
    214 2,16,17 16
    215 2,16,17 16
    217 2,16,17 16
    218 2,开发者_如何学JAVA16,17,26  16
    219 2,16    16
    220 16,17   16
    221 2,16    16

It checks for only one skill.

I want to select students which have all three skills.

Please help me out.

Thanks in advance.

-Aarti

Select S.StudentId, JP.MandSkills
from JobPosts JP
cross apply Split(JP.MandSkills, ',') JPS
inner join (
    select S.StudentId, S.MandSkills, SS.items
    from Students S
    cross apply Split(S.MandSkills, ',') SS) S on S.items = JPS.items
where JP.JobPostId=36
group by S.StudentId, JP.MandSkills
having LEN(JP.MandSkills)-LEN(replace(JP.MandSkills,',',''))+1 = COUNT(distinct S.items)

You can try this .. but I am guessing .. I do not know even if it is possible to do something like this:

select * 
from Student s
join Job j on (select count(*)-sum(case when jms.items is not null then 1 else 0 end)
               from split(j.MandSkills,',') sms
               left join split(s.MandSkills,',') jms on sms.items=jms.items)=0

I would recommend changing your schema to not store the list of skills as a CSV value in each table. Performance will suffer as you end up having to split the values out and no index will be used.

Instead, I'd create 2 extra tables:

StudentSkill
-------------
StudentId
SkillId

JobSkill
--------
JobPostId
SkillId

In each case, both fields form the Primary Key and reference the appropriate master table for each column (i.e. StudentId -> Student.StudentId)

Now you have a schema that can result in queries that are SARGable (can use indexes).

So then one query that should work for you is:

;WITH CTEJobSkills AS
(
SELECT SkillID 
FROM JobSkill
WHERE JobPostID = 1
)

SELECT s.StudentID 
FROM Student s
WHERE NOT EXISTS
    (
        SELECT *
        FROM CTEJobSkills js
            LEFT JOIN StudentSkill ss ON js.SkillID = ss.SkillID AND ss.StudentID = s.StudentID
        WHERE ss.SkillID IS NULL
    )

That's a fairly quick attempt, so there may be other ways to structure that query - worth playing around with if you can make the schema change.

try this one (I haven't tested it):

SELECT S.StudentId, S.MandSkills, JP.items
  FROM Students S
       , Split((select MandSkills from JobPosts where JobPostId=36),',') JP
 WHERE NOT EXISTS 
         (SELECT NULL
            FROM (SELECT JP1.items JP_items, S1.items S_items
                    FROM Split((select MandSkills 
                                  from JobPosts
                                 where JobPostId=36),',') JP1
                    LEFT JOIN Split(S.MandSkills,',') S1
                    ON JP1.items = S1.items) A
          WHERE S_items IS NULL)

SELECT sid FROM
    (
    Select sid, jid, ssk, jsk FROM
        (SELECT id sid, regexp_split_to_table(stud.skill, ',') ssk FROM stud) s,
        (SELECT id jid, regexp_split_to_table(job.skill, ',') jsk FROM job WHERE id = 36) j
    WHERE ssk = jsk
    ORDER BY sid, jsk, ssk
    ) jn
GROUP BY sid
HAVING count(*) = (SELECT count(*) FROM (SELECT id jid, regexp_split_to_table(job.skill, ',') jsk FROM job WHERE id = 36) sj) 

Sorry, I used POSTGRESQL for a quick check. regexp_split_to_table is equal to SPLIT in SQL Server. I woud also recomment to normalize your table struture further. Using one row per id->skill pair would get you rid of the SPLIT operation.