vendredi 14 juillet 2017

MYSQL left join (if id is in other table then 1 else 0)

i have a table EVENT_COMMENTS and EVENT_COMMENTS_LIKES, and everything works fine except that i want to check if i liked that comment already. So i do a left join with the likes table (has foreign keys (comment_id and user_id)) and if that user_id is my own id i want to output = true.

But it is not working at all. I mean i get the list with l.User_ID of all IDs (which i count for the amount of likes) but i cannot check if my id is in there. 54 is in this case my id.

SELECT c.Comment_ID,c.Event_ID,c.User_ID,count(l.User_ID) as amount_likes,

(case l.User_ID when 54 THEN 'true' ELSE 'false' END) as is_liked

FROM EVENT_COMMENTS c 
left join USERINFO u on u.USERID=c.User_ID
left join EVENT_COMMENTS_LIKES l on l.Comment_ID = c.Comment_ID
WHERE Event_ID=121514
GROUP BY c.Comment_ID
ORDER BY updated_time DESC

The bold one is the tricky one.

This is the error: Expression #10 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'markusp.l.User_ID' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Appreciate your help :)

Aucun commentaire:

Enregistrer un commentaire