SQL: Syntax error (Missing operator)

Question

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.

Your title says syntax error, but your question says "doesn't seem to work". What are you trying to accomplish and what problem are you facing? — Nivas
– Nivas, Commented Jun 11, 2014 at 13:47
I'm trying to get the output into the format shown, the last line is the line that gives errors. If I take out then I'm asked to input the Manager's name and it comes out in the correct format. The last line of the query is to get the manager's name, that's the problem I'm facing. — Darkestlyrics
– Darkestlyrics, Commented Jun 11, 2014 at 13:51

Michael Berkowski · Accepted Answer · 2014-06-11 13:54:16Z

3

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);

edited Jun 11, 2014 at 13:54

answered Jun 11, 2014 at 13:48

Michael Berkowski

271k47 gold badges450 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Taryn Over a year ago

Nice catch with the ms-access my eyes obviously failed me when reading the question.

Michael Berkowski Over a year ago

@bluefeet I missed it at first and went back to scan for it when I saw the nondescript error "Missing operator".

Darkestlyrics Over a year ago

Thank you very much this has been very helpful. I shall remember that of Access in future, should I not get a proper databasing program.

Michael Berkowski Over a year ago

@Darkestlyrics Get a proper one at your earliest convenience :) I used to build queries in the Access GUI to study how it laid out all the () groups, because it barely makes sense to me.

Darkestlyrics Over a year ago

I'm currently waiting to see what a future employer would use. I was given this in a test to see my understanding and research skills.

|

Collectives™ on Stack Overflow

SQL: Syntax error (Missing operator)

1 Answer 1

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related