How do I merge 4 tables and group by user_id

Question

I have a query:

SELECT u.user_id, f.eye_color, c.rating
FROM
user u LEFT JOIN connection c ON u.user_id = c.connection_user_id,
   user_field f,
   search s
WHERE
    s.search_id = 1 AND
    u.user_id = f.user_id AND
    s.user_id != u.user_id
GROUP BY
    u.user_id;

And this is the error it shows

Error Code: 1055. Expression #51 of SELECT list is not in GROUP BY clause and contains non aggregated column 'sos.c.rating' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

you are mixing your `JOIN` syntax, with both explicit `JOIN` keywords and comma-JOIN syntax; it looks to me like you have 4 tables, not 3; as such, it's difficult for both you and us to see what's really going on; please clarify your query and detail what you've tried so far to deal with the error message you are getting — landru27, Nov 09 '18 at 04:39

score 0 · Answer 1 · answered Nov 09 '18 at 06:03

You can try below

You mixed up explicit and implicit join AND GROUP BY is unnecessary as you are not aggregating any column

SELECT u.user_id, f.eye_color, c.rating
FROM
user u LEFT JOIN connection c ON u.user_id = c.connection_user_id,
   left join user_field f on u.user_id = f.user_id
   left join search s on s.user_id != u.user_id
WHERE
    s.search_id = 1

How do I merge 4 tables and group by user_id

1 Answers1