Nesting JOIN syntax error

[sougata666]

(SELECT Aviator.Prefix, Aviator.Number, Aviator.DateOfSeniority, Year([DateofSeniority]) 
FROM (Aviator INNER JOIN AviatorCourse ON Aviator.ID = AviatorCourse.AviatorID)
WHERE (((AviatorCourse.CourseName)=9) AND ((Year([DateofSeniority]))=2007))))
LEFT JOIN
(SELECT Aviator.Prefix, Aviator.Number, Aviator.DateOfSeniority, Year([DateofSeniority])
FROM (Aviator INNER JOIN AviatorCourse ON Aviator.ID = AviatorCourse.AviatorID)
WHERE ((Year([DateofSeniority]))=2007))
ON Aviator.ID=AviatorCourse.AviatorID;

I am getting a syntax error. I am basically trying to find IDs of people who belong to 2007 batch but have not undertaken a particular course (denoted by index 9, i.e. set of (A-B) where A=set of people in 2007 batch and B=set of people in 2007 batch who have undertaken the course.)


Where am I going wrong & can I simplify the code?
I read that INNER JOIN cannot be inside LEFT JOIN.
Also, there are multiple courses against each person so I have to take this roundabout route to get my answer.

 

[plog]

With all those parenthesis its hard for me to parse that thing, but here's what I see:

1. You have 15 left parenthesis, you have 16 right parenthesis. That's not right, you should have one left for every right.

2. I think you have a LEFT JOIN after the WHERE in your main query--that would be a syntax error. You can't have JOIN clauses come before WHERE clauses in a query.

3. Actually, looking at it closer it makes little sense. You have two SELECT clauses, but they are the same. You have 2 FROM clauses and they are the same. Your WHERE clauses are different, but I don't know why.

If those don't help, I would need sample data to demonstrate what you are trying to accomplish. Provide 2 sets:

A. Starting data from Aviator and AviatorCourse. Include table and field names and enough data to cover all cases.

B. Expected results based on A. Show what data you want your query to return when you feed it the data in A.

 

[sougata666]

Thanks for looking through it. I worked it out somewhat by using two different queries (top part and bottom part) and then creating a third query get the result. You are right, I have used too many parentheses making it unreadable.

Query 1:

SELECT Aviator.Number
FROM Aviator INNER JOIN AviatorCourse ON Aviator.ID = AviatorCourse.AviatorID
WHERE AviatorCourse.CourseName=9 AND Year([DateofSeniority])=2007;

Query 2:

SELECT  DISTINCT Aviator.Number
FROM Aviator INNER JOIN AviatorCourse ON Aviator.ID = AviatorCourse.AviatorID
WHERE Year([DateofSeniority])=2007;

query 3:

SELECT Query2.Number
FROM Query2 
LEFT JOIN Query1 
ON Query2.[Number] = Query1.[Number]
WHERE Query1.Number Is Null;

I have taken only one field to keep it less cluttered for easier understanding.
Now, I think it is easier this way but is it better if I make a single query to get the same result? If so, how?

 

[plog]

If you want me to help you any further I need sample data as described in my first post.