0

I'm trying to create a kind of contacts list based on whether the user has a message from them or not. I am trying this:

        SELECT *
                FROM chat
                INNER JOIN user_info ON user_info.userID = chat.senderID
                INNER JOIN user_login ON user_login.userID = chat.senderID
                WHERE receiverID = :senderID
                GROUP BY receiverID
                ORDER BY chat.messageID ASC

What I would like to happen is having all the IDs that are the same grouped together so when I use a foreach loop it doesn't return the same ID multiple times.

However I receive this error from my code:

Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'username.chat.messageID' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by in"

I have tried searching it up and I found a few people saying to remove "only_full_group_by" in the sql mode variable. I cannot do this it gives me an error saying "#1227 - Access denied; you need (at least one of) the SUPER privilege(s) for this operation"

Any help on solving this would be great.

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
Caleby
  • 11
  • 1
  • Selecting `*` doesn't make sense when you're grouping by `receiverID`, since there will be multiple chats with the same receiver. If you turn off `ONLY_FULL_GROUP_BY`, it will mix up values from different rows for that receiver, and the results will be meaningless. – Barmar Dec 09 '22 at 03:53
  • If you're trying to get the first chat for each receiver, see https://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group-mysql?rq=1 – Barmar Dec 09 '22 at 03:54

0 Answers0