duane phillips

Conceptually, not only your queries, but your entire application design
would be _easier_ with normalized tables. You are creating a monster the
other way.

Needed Tables:
candidates table
skills table
candidate_skills table that joins the other two to achieve the many to many
relationship

You do not need a separate language table, as it fundamentally just another
type of skill. If you must, add another table that houses "skill types" and
add that foreign key to the skills table to delineate the type which each
skillset belongs to. How far you normalize is really the issue, but at
least a basic normalization is certainly in order.

Each table should have a non-data related identity column, and the joins to
other tables should be via those ID columns, not data. Queries on such
would be no more complex than what you are currently trying to conjur up.
Do it right and save yourself loads of time later, especially when new
skills become available, especially when relating skills to individuals.
Then you will also avoid issues like trying to perform queries on variations
of entries that are technically the same, but spelled differently like ENG
vs. english, VB vs. visual basic vs. MSVB vs.... etcetera.

tblCandidate
CandidateID,PreferredName,_OtherContactFieldsEtc_
1,John,Someplace somwhere some phone some email etcetera
2,Sue,----
3,Sam,----

tblSkill
SkillID,SkillCode,Skill,SkillType
1,VBDN,MS Visual Basic DotNet,Programming Language
2,CSDN,MS C# DotNet,Programming Language
3,ENGL,English,Spoken Language
4,GRMN,German,Spoken Language
5,PRLD,Project Lead,Positions
6,ELPR,Entry Level Programmer,Positions
7,PRMN,Project Manager,Positions
8,SPAN,Spanish,Spoken Language
9,ASSO,Associates,School
10,BACH,Bachelors,School
11,MSTR,Masters,School
12,PHDS,PhD,School
13,TAPI,TAPI,Specialties
14,NADM,Network Admin,Positions
15,DBAD,DBA,Positions
16,DBAN,Data Analyst,Positions
17,ASPS,ASP,Programming Language
18,JSPS,JSP,Programming Language
19,PHPS,PHP,Programming Language
20,RDHT,Red Hat,O/S
21,WNXP,MS WinXP,O/S
22,SV03,MS Server 2003,O/S
23,DEBN,Debian,O/S
24,SUSE,SUSE,O/S
25,MNDK,Mandrake,O/S
26,SQLS,MS SQL Server,Database
27,MYSQ,MySQL,Database
28,ORCL,Oracle,Database
29,ACCS,MS Access (JET),Database
30,FOXP,Fox,Database
31,FRNC,French,Spoken Language
32,DNET,Dot Net (General),Programming Language
33,DTCH,Dutch,Spoken Language

tblCandidateSkill
CandidateSkillID,CandidateID,SkillID
1,1,2
2,1,4
3,1,20
4,1,9
5,2,8
6,2,3
7,2,6
8,2,19
9,3,10
10,3,16
11,3,28
12,3,26
13,3,11
14,3,17
15,3,33
16,3,4
17,3,19

So, given the sample data above, and using this query (this is just *one*
way to do this)

SELECT tblCandidate.*
FROM (((((((tblCandidate LEFT JOIN tblCandidateSkill ON
tblCandidate.CandidateID = tblCandidateSkill.CandidateID) LEFT JOIN tblSkill
ON tblCandidateSkill.SkillID = tblSkill.SkillID) LEFT JOIN tblCandidateSkill
AS tblCandidateSkill_1 ON tblCandidate.CandidateID =
tblCandidateSkill_1.CandidateID) LEFT JOIN tblSkill AS tblSkill_1 ON
tblCandidateSkill_1.SkillID = tblSkill_1.SkillID) LEFT JOIN
(tblCandidateSkill AS tblCandidateSkill_2 LEFT JOIN tblSkill AS tblSkill_2
ON tblCandidateSkill_2.SkillID = tblSkill_2.SkillID) ON
tblCandidate.CandidateID = tblCandidateSkill_2.CandidateID) LEFT JOIN
(tblCandidateSkill AS tblCandidateSkill_3 LEFT JOIN tblSkill AS tblSkill_3
ON tblCandidateSkill_3.SkillID = tblSkill_3.SkillID) ON
tblCandidate.CandidateID = tblCandidateSkill_3.CandidateID) LEFT JOIN
tblCandidateSkill AS tblCandidateSkill_4 ON tblCandidate.CandidateID =
tblCandidateSkill_4.CandidateID) LEFT JOIN tblSkill AS tblSkill_4 ON
tblCandidateSkill_4.SkillID = tblSkill_4.SkillID
WHERE (((tblSkill.SkillCode)="MSTR") AND ((tblSkill_1.SkillCode)="PHPS") AND
((tblSkill_2.SkillCode)="ASPS") AND ((tblSkill_3.SkillCode)="FRNC")) OR
(((tblSkill.SkillCode)="MSTR") AND ((tblSkill_1.SkillCode)="PHPS") AND
((tblSkill_2.SkillCode)="DNET") AND ((tblSkill_3.SkillCode)="FRNC")) OR
(((tblSkill.SkillCode)="MSTR") AND ((tblSkill_1.SkillCode)="PHPS") AND
((tblSkill_2.SkillCode)="ASPS") AND ((tblSkill_3.SkillCode)="DTCH") AND
((tblSkill_4.SkillCode)="GRMN")) OR (((tblSkill.SkillCode)="MSTR") AND
((tblSkill_1.SkillCode)="PHPS") AND ((tblSkill_2.SkillCode)="DNET") AND
((tblSkill_3.SkillCode)="DTCH") AND ((tblSkill_4.SkillCode)="GRMN"))
ORDER BY tblCandidate.PreferredName;

You would get (CSV formatted):
CandidateID,PreferredName,_OtherContactFieldsEtc_
3,Sam,----

Per the example data, Sam is one highly qualified individual <grin>.

Your query interface would further benefit by having the user select via
checkbox or somesuch, all desired skills, and instead of using SkillCode
values, use the SkillID of each.

There are, however, much better ways of doing this kind of search, more
technically advanced SQL and/or a Stored Procedure that would be optimized
to not be so hard on resources.

A GUI could also handle each criteria individually... etcetra... etcetra...

Please forgive any typos that may or may not exist in the lengthy reply
above...

HTH.

~ Duane Phillips.