0

I have MySQL JOIN in roughly the following format:

SELECT DISTINCT employees.empl_id,
                employees.name,
                departments.dep_id,
                departments.name,
                buildings.id         AS building_id,
                buildings.address    AS building_address
                buildings.department AS building_department
FROM employees
     LEFT JOIN employees USING (empl_id)
     LEFT JOIN departments USING (dep_id)
     LEFT JOIN buildings
            ON buildings.department = departments.dep_id

When I run this query I get the error:

ERROR 1054 (42S22) at line 1: Unknown column 'MyDB.employees.dep_id' in 'on clause'

Even tough the column dep_id really does not exists in employees, I never reference employees.dep_id in the JOIN, and dep_id is a column of departments and not employees!!

Can this be due to mixing the USING and ON clause?

This JOIN is part of a script I wrote which was done using MySQL version > 5, and no errors occurred. I am adapting the script to work with an older version of MySQL and ran into this error. mysql --version yields:

mysql  Ver 14.7 Distrib 4.1.22, for pc-linux-gnu (i686) using readline 4.3

I read through this but it did not help me. Any suggestions would be appreciated.

Community
  • 1
  • 1
Ian2thedv
  • 2,691
  • 2
  • 26
  • 47
  • You would be best served to *forget* about the existence of `USING` and stick with `ON` for joins. Similarly, never use `DISTINCT` without a specific and deliberate reason to do so. It often masks illogical or invalid queries and can harm the performance of properly-written queries when it is used but not needed. – Michael - sqlbot Jul 28 '15 at 09:46

1 Answers1

2

The point is that you are doing that join here:

LEFT JOIN departments USING (dep_id)

means 

LEFT JOIN departments ON employees.dep_id = departments.dep_id

and this is what is causing your error.

Lelio Faieta
  • 6,457
  • 7
  • 40
  • 74