CODEWITHSUNDEEP
mysqlsql
Thilina Sandaruwan
Thilina Sandaruwan

Reputation: 335

How to fix "1054 - unknown column on clause"

I wrote a sql code to get the data from my MySQL database. but when i run sql query it returns an error.

How an I fix this. Im new to this and I googled but i cant find a solution.

1054 - Unknown column 'departments.department_id' in 'on clause'

SELECT
    staff_data.first_name,
    staff_data.middle_name,
    staff_data.last_name,
    staff_data.gender,
    staff_data.civil_status,
    staff_data.birthday,
    staff_data.address_line_1,
    staff_data.address_line_2,
    staff_data.address_line_3,
    staff_data.contact_no_1,
    staff_data.contact_no_2,
    staff_data.nationality,
    staff_data.religion,
    staff_data.email,
    staff_data.is_deleted,
    staff_data.nic_number,
    staff_data.is_admin,
    departments.department_name,
    job_positions.position_name 
FROM
    departments,
    employee_position
    INNER JOIN job_positions ON job_positions.department_id = 
departments.department_id 
    AND departments.department_id = job_positions.department_id 
    AND employee_position.position_id = job_positions.position_id
    INNER JOIN staff_data ON employee_position.user_id = 
staff_data.nic_number 
WHERE
    employee_position.is_valid = 1

Upvotes: 2

Views: 5740

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521093

Please don't mix old school join syntax with modern syntax (the latter which you should be using only). Here is your corrected query using modern join syntax:

SELECT
    s.first_name,
    s.middle_name,
    s.last_name,
    s.gender,
    s.civil_status,
    s.birthday,
    s.address_line_1,
    s.address_line_2,
    s.address_line_3,
    s.contact_no_1,
    s.contact_no_2,
    s.nationality,
    s.religion,
    s.email,
    s.is_deleted,
    s.nic_number,
    s.is_admin,
    d.department_name,
    j.position_name 
FROM departments d
INNER JOIN job_positions j
    ON j.department_id = d.department_id 
INNER JOIN employee_position e
    ON e.position_id = j.position_id
INNER JOIN staff_data s
    ON e.user_id = s.nic_number
WHERE
    e.is_valid = 1;

The error you are seeing is probably coming from the fact that your use of old school join syntax put the MySQL parser into a certain mode, which is then not behaving as you expect subsequently.

Note that I also introduced table aliases, which makes the query easier to read.

Upvotes: 2

Vijiy
Vijiy

Reputation: 1197

Issue is with first join

SELECT
    staff_data.first_name,
    staff_data.middle_name,
    staff_data.last_name,
    staff_data.gender,
    staff_data.civil_status,
    staff_data.birthday,
    staff_data.address_line_1,
    staff_data.address_line_2,
    staff_data.address_line_3,
    staff_data.contact_no_1,
    staff_data.contact_no_2,
    staff_data.nationality,
    staff_data.religion,
    staff_data.email,
    staff_data.is_deleted,
    staff_data.nic_number,
    staff_data.is_admin,
    departments.department_name,
    job_positions.position_name 
FROM
    departments,
    employee_position
    INNER JOIN job_positions ON 
    job_positions.department_id = <change to req col>
    -- departments.department_id join between employee_position and job_positions, but join is on department table 
    AND departments.department_id = job_positions.department_id 
    AND employee_position.position_id = job_positions.position_id
    INNER JOIN staff_data ON employee_position.user_id = 
staff_data.nic_number 
WHERE
    employee_position.is_valid = 1

Upvotes: 0

Related Questions