Ambiguous joins in an unmatched query?

[MrsNic]

I have set up a database to keep track of the specific ICT skills pupils use in their coursework. All was going well until I tried to use an unmatched query to find any skills an individual pupil has not used.


It keeps telling me I have ambiguous joins

tables used

pupil (1-m) coursework(1 -m) coursework skill(m - 1)skills

sql used

SELECT Coursework.CourseworkIDNumber, CourseworkSkills.Student, CourseworkSkills.SkillID, Skills.Description
FROM Skills LEFT JOIN (Coursework INNER JOIN CourseworkSkills ON Coursework.CourseworkIDNumber = CourseworkSkills.Student) ON Skills.SkillID = CourseworkSkills.SkillID
WHERE (((CourseworkSkills.SkillID) Is Null));

I thought it was going to be so simple!!

 

[mikey69]

I don't think that SQL can evaluate an inner join within a left join. I think you may need to redesign your tables to allow two left joins.

Access makes all things possible. It even makes them intelligible

 

[PHV]

find any skills an individual pupil has not used
Perhaps something like this ?
SELECT Coursework.CourseworkIDNumber, CourseworkSkills.Student, Skills.SkillID, Skills.Description
FROM Skills, Coursework INNER JOIN CourseworkSkills ON Coursework.CourseworkIDNumber = CourseworkSkills.Student
WHERE CourseworkSkills.SkillID Not In (SELECT SkillID FROM Skills)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886