CODEWITHSUNDEEP
sqlms-access
Darkestlyrics
Darkestlyrics

Reputation: 310

SQL: Syntax error (Missing operator)

I'm not that good at SQL at all

I have two tables in an MS access database 

|ID| Name  |Surname|Postion|           |EmpID|ManID|
----------------------------           ------------- 
|1 |Scrooge|McDuck |Manager|           |3    |1    |
|2 |Daisy  |Duck   |Manager|           |7    |1    |
|3 |Donald |Duck   |Support|           |6    |2    | 
|4 |Minny  |Mouse  |Support|           |4    |2    |
|5 |Mickey |Mouse  |Support|           |2    |1    |
|6 |Goofy  |       |Support|           |1    |2    |
|7 |Pluto  |       |Support|           |5    |2    |
|8 |Huey   |Duck   |Support|
|9 |Dewey  |Duck   |Support|
|10|Louie  |Duck   |Support|

I need to write an SQL statement to produce the following output

| Name  |Surname|Postion|Manager Name|Manager Positon|          
------------------------            
|Donald |Duck   |Support|Scrooge     |Manager         
|Pluto  |       |Support|Scrooge     |Manager         
|Goofy  |       |Support|Daisy       |Manager        
|Minny  |Mouse  |Support|Daisy       |Manager       
|Daisy  |Duck   |Support|Scrooge     |Manager        
|Scrooge|McDuck |Manager|Daisy       |Manager        
|Mickey |Mouse  |Manager|Daisy       |Manager

My code looks like this so far (I've been looking on the net to see how it's done and why)

SELECT Employee.Name,Employee.Surname,Employee.Position,Manager.Name as ManagerName
FROM Employee
INNER JOIN Stafflink ON Employee.ID=Stafflink.EmpID
INNER JOIN Employee Manager ON Manager.ID=Stafflink.ManID;

I know the question was answered in Sql table output

But It doesn't seem to work and generates the error:

Syntax error (Missing operator)

Any assistance would be greatly appreciated.

Upvotes: 2

Views: 262

Answers (1)

Michael Berkowski
Michael Berkowski

Reputation: 270609

MS Access has an atypical requirement for multiple JOINs that they be enclosed in nested () groups like:

FROM
  ((t1 INNER JOIN t2 ON t1.id = t2.id)
     INNER JOIN t3 ON t2.id = t3.id)

Your FROM clause should be expressed as:

SELECT Employee.Name,Employee.Surname,Employee.Position,Manager.Name as ManagerName
FROM ((
  Employee
  INNER JOIN Stafflink ON Employee.ID=Stafflink.EmpID)
  INNER JOIN Employee Manager ON Manager.ID=Stafflink.ManID);

Upvotes: 3

Related Questions