Cfrgtkky

I am trying to retrieve information from a mysql database.
I have the following tables:

Qualifications(qualificationid, qualificationname, personid,status)

Address(addressid, addressline1,city,province,areacode,personid)

score(scoreid, score.choices,personid,jobid)

I use typed the following mysql statement to retrieve the data

SELECT score.personid, qualifications.qualificationname, score.score

FROM

Qualifications, Score, Address

WHERE

score.jobid=58

AND

qualifications.qualificationName ='Human Resource Management'

AND

aadress.province ='Western Cape'

ORDER BY score.score

LIMIT 0,20;

this seems to work for everything else but doesn't restrict the province to western cape.

Why don't you use joins? Like so:

SELECT s.personid, q.qualificationname, s.score

  FROM Score s

  INNER JOIN Qualifications q ON q.personid = s.personid AND q.qualificationName ='Human Resource Management'

  INNER JOIN Address a ON a.personid = s.personid AND a.province ='Western Cape'

  WHERE s.jobid = 58

  ORDER BY s.score DESC

  LIMIT 0,20;

You will need to define relations. The system now has no clue how Addresses relate to Scores or Qualifications in your example. By adding a GROUP BY score.personid and AND score.personid = address.personid and score.personid = qualifications.personid you might fix your problems.

Also, using JOINS is probably more efficient as it does basically the same.