CODEWITHSUNDEEP
ms-accessjoindb2ms-access-2007subquery
hacket
hacket

Reputation: 1161

Syntax error on join sub-select

I have 4 fields that I need ->

  1. USERID
  2. LOCATION
  3. REPORT_MGR_USERID (reporting manager user ID)
  4. Manager Location (this is not actually stored)

To get the 4th one, I need to build it using SQL. I can do this via the "USERID" and "LOCATION" fields.

Here is my attempt. I am running a query within Access 2007, via a linked DB2 table.

SELECT DISTINCT employee_table.LOCATION, employee_table.USERID, employee_table.REPORT_MGR_USERID,  manager_location 
FROM employee_table main
JOIN  employee_table (SELECT DISTINCT employee_table.LOCATION FROM employee_table AS sub WHERE sub.USERID = main.REPORT_MGR_USERID) manager_location

When I run it, it's giving me this error ->

Syntax error in FROM clause

and then highlights the JOIN keyword.

This looks correct from everything I've looked at....

EDIT:

Thanks to David W, this is the working version for future reference ->

SELECT DISTINCT main.LOCATION, main.USERID, main.REPORT_MGR_USERID,  manager_data.LOCATION 
FROM employee_table main
INNER JOIN employee_table manager_data
ON main.REPORT_MGR_USERID=manager_data.USERID

Upvotes: 1

Views: 613

Answers (2)

David W
David W

Reputation: 10184

You are specifying both a table name employee_table and a subselect (SELECT DISTINCT....) in the first JOIN clause. And there's no ON condition, although that may be what you want?

The specification of employee_table in that JOIN might just be a typo, since you've provided an alias for the subselect...

Edit The light bulb just went on and I understand what the OP wants. Here we go:

SELECT DISTINCT main.LOCATION, main.USERID, main.REPORT_MGR_USERID, manager_data.manager_location 
   FROM employee_table main
   INNER JOIN employee_table manager_data
     on main.REPORT_MGR_USERID=manager_data.USERID

Upvotes: 2

HansUp
HansUp

Reputation: 97101

The Access db engine doesn't recognize JOIN as a synonym for INNER JOIN. It throws a syntax error any time you use just JOIN alone. Change it to INNER JOIN.

However, I suspect you may still have trouble after that correction. The remainder of the join expression is confusing and I don't understand what it's intended to accomplish. So after fixing the JOIN issue, you may still get another syntax error.

Upvotes: 1

Related Questions