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, Jun 11 '14 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, Jun 11 '14 at 13:51

Michael Berkowski · Accepted Answer · 2014-06-11T13:54:16.080

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 '14 at 13:54

answered Jun 11 '14 at 13:48

Michael Berkowski

267,341
46
444
390

Nice catch with the `ms-access` my eyes obviously failed me when reading the question. – Taryn Jun 11 '14 at 13:50
@bluefeet I missed it at first and went back to scan for it when I saw the nondescript error "Missing operator". – Michael Berkowski Jun 11 '14 at 13:51
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. – Darkestlyrics Jun 11 '14 at 13:59
@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. – Michael Berkowski Jun 11 '14 at 14:03
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. – Darkestlyrics Jun 11 '14 at 14:11
Quick question, If I wanted to include the 3 employees that do not have Managers (Huey, Dewey and Louie) How would I go about that? – Darkestlyrics Jun 11 '14 at 14:43
@Darkestlyrics `LEFT JOIN` between `Employee` and `Manager` should do it. See [this excellent visual explanation of join types](http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/) by one of this site's founders – Michael Berkowski Jun 11 '14 at 14:49
I thought of that just after I posted. Also, is there a way to take a blank field and make it a say something instead? e.g. if the field is blank could I make it say "Null". Sorry for the comments spam, I don't have enough reputation for Chat yet. – Darkestlyrics Jun 11 '14 at 15:07
@Darkestlyrics Standard SQL uses `COAESCE(maybe_empty_column, 'Default value if null')` for that, but Access apparently doesn't. See http://stackoverflow.com/questions/247858/coalesce-alternative-in-access-sql - I guess there's an `Nz()` function? Or `IFNULL()` – Michael Berkowski Jun 11 '14 at 15:09

SQL: Syntax error (Missing operator)

1 Answers1