Compare entity attributes to a list of values

[baygins]

Ok, here is my problem:
I have a table like so:
CANDIDATE_HAS_SKILLS
----------------------------------------------
id_candidate_has_skills, id_candidate, id_skill

In this table, I keep a list of candidate and their skills. I have a comma separated list of skill ID's in a variable that I want to use to return the list of candidates who have EXACTLY the list of skills from that variable.

So, for example, if the table was so:

id_candidate_has_skills, id_candidate, id_skill
--------------------------------------------
1 1 1
2 1 2
3 1 4
4 2 1
5 2 4
6 3 1

And my query parameter was "1, 4", meaning I would like to find candidates whose skill ID's are "1 AND 4".
I want the returned list of candidate ID's to be "1" and "2", and not "3".

How can I create a query that returns me the list of candidate ID's whose skills EXACTLY match (no less, no more, no in between) the list of skill ID's in my variable ("1, 4")?

Thank you,
--selim

[skhanal]

You can write a table valued UDF to convert comma separated list to return a table and compare that to the skills table.

[baygins]

But how do I do the comparison? Let's say the list of skills that need to be matched are like this:

skills
----------
1
4

and the data is in the candidate_has_skills table

id_candidate_has_skills, id_candidate, id_skill
--------------------------------------------
1                        1              1
2                        1              2
3                        1              4
4                        2              1
5                        2              4
6                        3              1

How do I return the list of "id_candidate"s from "candidate_has_skills" table whose "id_skill"s that EXACTLY MATCH the skills from the "skills" table?

Thanks for your help,
--selim

[mikr0s]

--Queries demonstrated for MSSQL, T-SQL
--the following query works if you know the number of skills required,

--for example for two (2) skills
SELECT id_candidate
FROM CANDIDATE_HAS_SKILLS
WHERE ','+'1,4'+ ',' LIKE '%,'+CAST(id_skill AS varchar(5))+',%'
GROUP BY id_candidate
HAVING COUNT(id_skill) = 2

--for three (3) skills
SELECT id_candidate
FROM CANDIDATE_HAS_SKILLS
WHERE ','+ '1,4,5'+ ',' LIKE '%,'+CAST(id_skill AS varchar(5))+',%'
GROUP BY id_candidate
HAVING COUNT(id_skill) = 3

--and so on

--HTH--

[baygins]

Wow, this is clearly "advanced" DBA stuff compared to my skill set, but none the less, much appreciated.
However, my app does not alway guarantee that the list of skill IDs are going to be constant in the query.
Here is the situation:
My HR manager enters a list of potential candidates into the system, and this info is kept in 2 MySQL tables: (1) candidates, and (2) candidate_has_skills
When the web form is submitted, the PHP code inserts the candidate info into the "candidates" table and also the list of skills (could be any number of skills for any candidate) into the "candidate_has_skills" table.
So, when she wants to find people that have an exact set of skills, she could go the search form and create a query that essentially says "give me the list of candidate IDs that exactly have skill IDs 1 and 4". The last part of that query (the list of skill IDs) could be different in every query she runs.
In one instance she could be looking for people who have "C++ epxerience, or skill ID = 1"; in another instance she could be looking for people with "C++, Java, ASP, MySQL, PHP, or 1,2,3,4,5".
So, you see, the query parameter of the list of skill IDs is variable in nature. What an oxymoron? A variable that is variable in size and nature :-)

Thanks,
--selim