K
K
Kanat2019-03-16 11:07:39
MySQL
Kanat, 2019-03-16 11:07:39

How to make query from 3 tables with group by?

SELECT `private_dialogs`.*, `users`.title, `private_messages`.text FROM `private_dialogs` LEFT JOIN `users` ON `private_dialogs`.to_id = `users`.user_id 
    LEFT JOIN `private_messages` ON `private_messages`.dialog_id = `private_dialogs`.dialog_id WHERE `private_dialogs`.`from_id` = :user_id GROUP BY `private_messages`.dialog_id

I piled garbage here)) gives an error
Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column

Answer the question

In order to leave comments, you need to log in

1 answer(s)
R
retvizan, 2019-03-16
@retvizan

After select without group functions, there can only be fields that are grouped (because it is not clear for the rest which value to choose from the group).
If you know for sure that duplication is a consequence of join and all fields in the group are the same, then you can simply turn off the ONLY_FULL_GROUP_BY mode.
Or is it better to immediately rewrite the query in the correct way, there are at least 5 options, see Grouping in MySQL

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question