1054 Unknown Column in Full Join, but not other joins
Why am I getting the error "1054 Unknown Column" from the following mysql syntax:
SELECT attorney_first_name, attorney_last_name, att_school, att_degree_obtained
FROM jos_legalally_attorneys
FULL JOIN jos_legalally_attorney_education
ON jos_legalally_attorneys.user_id=jos_legalally_attorney_education.user_id;
This results in an error:
#1054 - Unknown column 'jos_legalally_attorneys.user_id' in 'on clause'
I only get this error when trying FULL JOIN
. All other joins (Inner, Right, Left) succeed using this exact syntax. (I've tried an option FULL OUTER JOIN
).
source to share
Reported here , MySQL does not support FULL JOIN
, so your query is interpreted like this:
SELECT attorney_first_name, attorney_last_name, att_school, att_degree_obtained
FROM jos_legalally_attorneys as FULL #FULL is taken as table alias!!
INNER JOIN jos_legalally_attorney_education
ON jos_legalally_attorneys.user_id=jos_legalally_attorney_education.user_id;
Now, since it has a FULL
table alias for jos_legalally_attorneys
, it doesn't work as expected.
If you try the following query, it shouldn't give any errors (even if it doesn't do what you want):
SELECT attorney_first_name, attorney_last_name, att_school, att_degree_obtained
FROM jos_legalally_attorneys
FULL JOIN jos_legalally_attorney_education
ON FULL.user_id=jos_legalally_attorney_education.user_id;
Now, to get FULL JOIN
what you need in MySQL, you need to do something like this (as shown here ):
SELECT attorney_first_name, attorney_last_name, att_school, att_degree_obtained
FROM jos_legalally_attorneys
LEFT JOIN jos_legalally_attorney_education
ON jos_legalally_attorneys.user_id=jos_legalally_attorney_education.user_id
UNION ALL
SELECT attorney_first_name, attorney_last_name, att_school, att_degree_obtained
FROM jos_legalally_attorneys
RIGHT JOIN jos_legalally_attorney_education
ON jos_legalally_attorneys.user_id=jos_legalally_attorney_education.user_id
source to share