CODEWITHSUNDEEP
mysqlcoalesce
pollux1er
pollux1er

Reputation: 5919

COALESCE function in MYSQL with select gives error

I am wondering what is the problem of my query which is simple for me, please also have a look and please let me know if I missed something

SELECT users.*, p.*, 
COALESCE(SELECT picture_location FROM pictures 
WHERE user_id = 'patient' AND default_pic = 1, 
SELECT picture_location FROM pictures 
WHERE user_id = 'patient' ORDER BY id ASC LIMIT 1) AS default_pic, 
pic.picture_location, MONTHNAME(users.registered_on) AS month_reg,
YEAR(users.registered_on) AS year_reg FROM users 
LEFT JOIN profile p ON p.profile_id = users.profile_id 
LEFT JOIN pictures pic ON pic.user_id = p.profile_id 
WHERE users.pseudo = 'patient' GROUP BY users.pseudo 
ORDER BY pic.default_pic DESC LIMIT 1

Do you see something ? It returns me this :

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT picture_location FROM pictures WHERE user_id = 'patient' AND default_pic ' at line 1

Upvotes: 0

Views: 757

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

A nested select needs an additional layer of parentheses:

SELECT users.*, p.*,
       COALESCE((SELECT picture_location FROM pictures WHERE user_id = 'patient' AND default_pic = 1),
                (SELECT picture_location FROM pictures WHERE user_id = 'patient' ORDER BY id ASC LIMIT 1)) AS default_pic,
       pic.picture_location,
       MONTHNAME(users.registered_on) AS month_reg, YEAR(users.registered_on) AS year_reg
FROM users LEFT JOIN
     profile p
     ON p.profile_id = users.profile_id LEFT JOIN
     pictures pic
     ON pic.user_id = p.profile_id
WHERE users.pseudo = 'patient'
GROUP BY users.pseudo
ORDER BY pic.default_pic DESC
LIMIT 1;

Upvotes: 2

Related Questions