MySQL unknown column in ON clause
Don't mix ANSI-89 style and ANSI-92 style joins. They have different precedence which can lead to confusing errors, and that is what has happened here. Your query is being interpreted as follows:
FROM property p, (
propertygeometry pg
JOIN shortlist sl ON sl.PropertyID = p.id AND sl.MemberID = 384216
...
)
In the above, the joins using the JOIN keyword are evaluated first before the comma-style join is even considered. At that point the table p
isn't yet declared.
From the MySQL manual:
However, the precedence of the comma operator is less than of INNER JOIN, CROSS JOIN, LEFT JOIN, and so on. If you mix comma joins with the other join types when there is a join condition, an error of the form Unknown column 'col_name' in 'on clause' may occur. Information about dealing with this problem is given later in this section.
I'd recommend always using ANSI-92 style joins, i.e. using the JOIN keyword:
SELECT p.*,
IF(COUNT(ms.PropertyID) > 0,1,0) AS Contacted,
pm.MediaID,
date_format(p.AvailableFrom, '%d %b %Y') AS 'AvailableFrom',
astext(pg.Geometry) AS Geometry
FROM property p
JOIN propertygeometry pg ON p.PropertyGeometryID = pg.id
JOIN shortlist sl ON sl.PropertyID = p.id AND sl.MemberID = 384216
LEFT JOIN message ms ON ms.PropertyID = p.id AND ms.SenderID = 384216
LEFT JOIN property_media pm ON pm.PropertyID = p.id AND pm.IsPrimary = 1
WHERE p.paused = 0
GROUP BY p.id
Related:
- Why isn't SQL ANSI-92 standard better adopted over ANSI-89?
How to fix 1054 - unknown column on clause
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.
MySQL Unknown Column in On Clause
because your mix join syntax
From Mysql
[docs]
However, the precedence of the comma operator is less than of INNER
JOIN, CROSS JOIN, LEFT JOIN, and so on. If you mix comma joins with
the other join types when there is a join condition, an error of the
form Unknown column 'col_name' in 'on clause' may occur. Information
about dealing with this problem is given later in this section.
the solution is:
To allow the join to be processed, group the first two tables
explicitly with parentheses so that the operands for the ON clause are
(t1,t2) and t3:SELECT * FROM (t1, t2) JOIN t3 ON (t1.i1 = t3.i3);
Alternatively, avoid the use of the comma operator and use JOIN
instead:SELECT * FROM t1 JOIN t2 JOIN t3 ON (t1.i1 = t3.i3);
mysql - Unknown Column In Where Clause
You have missed the punctuationmark symbol in where clause,correct use is: (punchmark)tablename(punchmark).(punchmark)columnname(punchmark). Please try with this:
SELECT
user_feedback
.user_id
,
user_feedback
.feedback_status
,
registered_user_detail
.user_id
FROM
user_feedback
,
registered_user_detail
WHERE
registered_user_detail
.user_id
= user_feedback
.user_id
AND user_feedback
.feedback_status
= 1
ORDER BY
user_feedback
.feedback_id
DESC
MySQL Error Unknown column in 'on clause'
Maybe this is the cause:
LEFT JOIN a ON a.a_id = b.a_id
should probably be
LEFT JOIN a ON a.a_id = b.b_id
and (?)
LEFT JOIN c ON c.c_id = b.b_id
Unknown column ' ' in 'where clause [MySQL]
You must use multiple-table UPDATE syntax:
UPDATE `wp_posts` as pm
JOIN `wp_postmeta` as pk ON pm.id = pk.post_id
AND pk.meta_key = '_job_description'
SET pm.post_content = pk.meta_value;
See https://dev.mysql.com/doc/refman/8.0/en/update.html
Related Topics
Copy Tables from One Database to Another in SQL Server
What Are Best Practices For Multi-Language Database Design
Group by Behavior When No Aggregate Functions Are Present in the Select Clause
Oracle SQL - How to Retrieve Highest 5 Values of a Column
How to Set Table Name in Dynamic SQL Query
SQL Performance on Left Outer Join VS Not Exists
What Is Sysname Data Type in SQL Server
Using Isnull VS Using Coalesce for Checking a Specific Condition
Can a Check Constraint Relate to Another Table
Database Structure For Tree Data Structure
Pass Multiple Values in Single Parameter
Sql: Find the Max Record Per Group
Sql: Creating a Relational Table With 2 Different Auto_Increment
How to Use (Install) Dblink in Postgresql
How to Determine the Last Day of the Previous Month Using Postgresql