logic mistake - please help me find it!!

[frogggg]

This is my sql statement. It should retrieve all records which have the specified location, jobcategory, career, and skill for one candidate. Instead, it invents a record that's mix and match. The location, jobcategory, career and skill specified, but that are not all related to one candidate! For example, the location, jobcategory, and career desired belong to one candidate, and the skill belongs to another, it displays all the requested info, with the candidateID of the last candidate, if that makes any sense.
Here's my sql statement. Can anyone find my logic mistake? Please!!??

SELECT JobCategory.JobCatName, CareerLevel.CareerName, CandidateContactInfo.CandidateID, Locations.LocationName, SkillCategory.SkillName " _
& "FROM CandidateContactInfo, CandidateJobCategory, JobCategory, CareerLevel, Locations, CandidateLocations, SkillCategory, CandidateSkills " _
& "WHERE CandidateJobCategory.JobCatID IN (" & jobCategories & &quot" _
& "AND CandidateJobCategory.JobCatID = JobCategory.JobCatID " _
& "AND CandidateContactInfo.CareerLevel = CareerLevel.CareerID " _
& "AND CandidateContactInfo.CareerLevel IN (" & careers & &quot" _
& "AND CandidateLocations.LocationID IN (" & locations & &quot" _
& "AND CandidateLocations.LocationID = Locations.LocationID " _
& "AND CandidateSkills.SkillID = SkillCategory.SkillID " _
& "AND CandidateSkills.SkillID IN (" & skills & &quot" _
& "AND CandidateContactInfo.CandidateID = CandidateJobCategory.CandidateID

Thanks in advance!

 

[derren]

Why are you using the " IN " clause and where do the variables there come from?

One of the things is that you are creating large theta joins on your related tables. If you were to set up the sql using joins you may have more luck Derren
[Mediocre talent - spread really thin]