Error when using MySQL IFNULL() with SELECT query-CodePudding

Please check the below MySQL Code.

SELECT seller_job_invites.idseller_job_invites,
seller_job_invites.idjob,
seller_job_invites.iduser as seller_id,
seller_job_invites.idjob_status,
user.iduser AS buyer_id,
user.first_name AS buyer_first_name,
user.last_name as buyer_last_name,
user.profile_picture AS buyer_profile_picture,
job.idjob_subcategories,
job.posted_date,
job.start_date,
job.location_lat,
job.location_long,
(SELECT IFNULL((SELECT translations.word FROM translations WHERE translations.dtype="JOB_SUB_CATEGORY" AND translations.language_code="FR" AND translations.fkey=job.idjob_subcategories) AS subcategory_name,(SELECT translations.word FROM translations WHERE translations.dtype="JOB_SUB_CATEGORY" AND translations.language_code="EN" AND translations.fkey=job.idjob_subcategories) AS subcategory_name)),
job_status.name AS 'job_status'
FROM peresia.seller_job_invites
INNER JOIN job ON seller_job_invites.idjob = job.idjob
INNER JOIN user ON job.iduser = user.iduser 
INNER JOIN job_status ON seller_job_invites.idjob_status = job_status.idjob_status
WHERE seller_job_invites.iduser=52 AND (job_status.name = 'SELLER_SCREENING' OR job_status.name ='SELLER_INVITED')

Please pay attention to the sub query I am using. What I am trying to do is return the French translation of the word, if it is NULL then return the English word.

This gives me the below error.

Error Code: 1583. Incorrect parameters in the call to native function 'ifnull'

How can I solve this?

CodePudding user response：

You appear to be assigning aliases within your IFNULL call. Remove them and add a single alias outside the function call.