MySQL Table Joins ....

Anon

Hello,
I Have a peculiar problem with mysql table joins.
Can anybody give me a solution for this.

My Table Structure:

ApplicantTab:

ApplicantCode ApplicantName
1000 Danny
1001 Guru

ApplicantSkillTab:

ApplicantCode SkillCode
1000 1
1000 2
1000 3
1001 1

I want to find a Applicant with both the Skills 1,2 , how do i write a query to do this.

$query = "find a person with skills 1,2";

i,e my expeted result is

ApplicantCode
1000

can somebody tell me what $query should be in the above problem.

thanks,
Danny ...

lucidfx

$result=mysql_query("SELECT * FROM Applicantab, appskilltab WHERE ((appskilltab.skillcode=1 OR appskilltab.skillcode=2) AND Applicantab.applicantcode=Applicantskilltab.applicantcode)");

the table names and stuff aren't exact but look through this and change it to the right stuff, youll get the idea. This should work.

Anon

i think you didn't get my problem right ...

i had clearly mentioned in my problem that i want a "AND" match and not a "OR".

if i use your query i get candidates with either skill 1 or skill 2.

i want a candidate whose is having both the skills i,e skill 1 and Skill 2.
and not those who are having Skill 1 or Skill 2.

When we join tables we do it row by row ... so using a or is simple ...

but i am matching a single entry in Table 1 to Multiple entries in Table 2.

i,e 11 and n1 ...

My Table Structure:

ApplicantTab:

ApplicantCode ApplicantName
1000 Danny
1001 Guru

ApplicantSkillTab:

ApplicantCode SkillCode
1000 1
1000 2
1000 3
1001 1

$query = "find a person with both skills

1,2";

(join the tables such a way that i get a candidate with both the skills)

i,e my expeted result is

ApplicantCode
1000

Any Comments ....

andrex

I beleive you could do it like this...

MYSQL_QUERY("select distinct ApplicantTab.ApplicantName, ApplicantTab.ApplicantCode, ApplicantSkillTab.ApplicantCode FROM ApplicantSkillTab, ApplicantTab WHERE ApplicantSkillTab.ApplicantCode=ApplicantTab.ApplicantCode AND ApplicantSkillTab.SkillCode=1 AND ApplicantSkillTab.SkillCode=2")

Good luck..

/ Andrex

Anon

even this query doesn't work out ...
it still gives me empty set of results ....