Running where on joined tables

[plazma]

Hi Guys, i really hope someone out there will be able to help me, i have been trying to figure this one out for a week.

 

i have a couple of tables named,

Candedates

Skills

CandedateSkills

 

The aim of this page is to match a cendadates skills with a particular job.

each candedate has and id

ID    Candedate

1      Nick

2      Terry

3      James

In the skills table is a list of skills each having an id

for example

ID    Skill

1    math

2    english

3    science

 

in the candedateSkills are records with the candedats id and the skills that they have and their grade

Candedate    Skill    Grade

1                  1          4

1                  2          4

1                  3          4

meaning that Nick's skills are maths, english and science.

 

What i am tring to achieve is a sql statement where i can ask show me all the candedates where their skills are english grades maths and science with their grade,

 

in a plain english version of the query would be: tell me all the candedates who have a grade 4 in english and a grade 4 in science and a grade 4 in science

 

Thanks in advance, ive been reading the forum but have yet to find something that fits my particular situation

 

[zenag]

can u show  what are all the fields of

Candedates

Skills

CandedateSkills

 

[plazma]

Oh i thought i already had.

on the candedates table

 

ID    Candedate

1      Nick

2      Terry

3      James

 

on the skills table

 

ID    Skill

1    math

2    english

3    science

 

on the candedate skills table

 

Candedate    Skill    Grade

1                  1          4

1                  2          4

1                  3          4

 

Thanks for the quick reply

[zenag]

SELECT candedates.candedate,skills.skill,cande.grade FROM candedates,cande,skills WHERE candedates.id=cande.candedate and cande.skill=skills.id

[zenag]

u can also use left join

 

[zenag]

SELECT candedates.candedate,skills.skill,cande.grade FROM candedates LEFT JOIN cande ON candedates.id=cande.candedate LEFT JOIN  skills ON cande.skill=skills.id WHERE ...any condition ...

[plazma]

Thanks for the reply zenag,

Ive tried the code out and am still running into some problems in the where clause.

Because i would be looking for something like

SELECT STATEMENTS HERE

where

Skill = 1(the skill ID) and grade = 1 (the value of the grade)

 

But there may be many criteria like

Skill = 1(the skill ID) and grade = 1 (the value of the grade)

Skill = 2(the skill ID) and grade = 4 (the value of the grade)

Skill = 3(the skill ID) and grade = 2 (the value of the grade)

and only supply a record if all criteria is met

 

Just not sure how to work out the where part of the statement, i appreciate the help

[fenway]

I'm not sure what you mean... you mean you want all matching pairs of (skillID, gradeValue)? That's not "all" criteria... unless you want only the ones where at least one row matches each one? Please clarify.

[plazma]

Thanks for the replies everyone,

yeah i am trying to filter out the records where all the skillID and gradeValue criteria pairs are met,

So far what i have is

 

select * from candedates join candedateskills on candedateskills.candedateid = candedates.id  join skills on

skills.id = candedateskills.skillid where (candedateskills.skillid = '2' and skillvalue = '1') and (candedateskills.skillid = '10' and skillvalue = '1')

I think i may be taking the wrong approach

[zenag]

where (candedateskills.skillid = '2' and skillvalue = '1') and (candedateskills.skillid = '10' and skillvalue = '1') 

(specify TABLE NAME BEFORE  skillvalue in where clause like candedateskills.skillid )

[fenway]

It can't be equal to two things at once; you need to use OR, and the check the count in a having clause.