-4

Hello the wonderful community,

I have a basic config MySQL / PHP with this DB :

  • Pages Table
  • Tags Table
  • Pages_Tags Table

Query : 

SELECT *, GROUP_CONCAT('tags_name')
FROM pages 
LEFT JOIN pages_tags ON pages_tags.pages_id = pages.pages_id
LEFT JOIN tags ON tags.tags_id = pages_tags.tags_id 
GROUP BY pages_tags.pages_id;

and i have the following error : 

"Expression #1 of SELECT list is not in GROUP BY clause and contains
 nonaggregated column this is incompatible with sql_mode=only_full_group_by"

So i made a lot of researches, and seems there are 2 solutions :

1/ To change the SQL mode 2/ To have the same column in the SELECT & GROUP BY.

Both seems bad solutions. The first because i don't want to change the default config and the second is insane, i need a lot of columns in the SELECT and not only the GROUP BY. Especially if the query is more complicated with a lot more LEFT JOIN. I need to display all theses infos.

Do you have any solutions ? Alternative with other methods ? I'm open to anything ;)

Thanks a lot !!

Tony
  • 604
  • 2
  • 6
  • 17
Coool6
  • 55
  • 6
  • show us some sample data – Nikhil S Nov 29 '18 at 07:52
  • Sample data of what ? It's a basic structure : Pages Table with hundred columns, Tags Table with few columns and a joint table to connect both cause a page can be linked to multiple tags. I need to have a list of the pages with all the tags linked listed in one field. – Coool6 Nov 29 '18 at 08:09

1 Answers1

0

starting from mysql 5.7 you can use column in select not involved in aggreagtion function and not mentioned in group by clause, so you should not use * (all) for column but add explict column name in select and mention the column in select not involved in aggregation function in group by 

    SELECT `pages_tags`.`pages_id`, GROUP_CONCAT('tags_name')
    FROM `pages` 
    LEFT JOIN `pages_tags` ON `pages_tags`.`pages_id` = `pages`.`pages_id` 
    LEFT JOIN `tags` ON `tags`.`tags_id` = `pages_tags`.`tags_id` 
    GROUP BY `pages_tags`.`pages_id`
ScaisEdge
  • 131,976
  • 10
  • 91
  • 107
  • Thanks ! But it doesn't help me. Because like i said at the end of my post, i need more informations than that. I need all the table columns. It's why i wrote "SELECT *" so how i can do ? – Coool6 Nov 29 '18 at 04:40
  • Then update your question and add a proper data sample and the expected result – ScaisEdge Nov 29 '18 at 06:43
  • The expected result is simple : i want all the datas ! Not just "`pages_tags`.`pages_id`, GROUP_CONCAT('tags_name')", i need "*, GROUP_CONCAT('tags_name')" And i'm looking for a solution to get all the data. I need all the columns of the PAGES table. – Coool6 Nov 29 '18 at 07:43
  • 1
    You have post a question for an error and with my answer the error si not more present .. so i think my answer is the right solution at your problem "Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column this is incompatible with sql_mode=only_full_group_by". Due the fact that we don't know your data struct and what you really need if someone ask for better explain i think you should answer addinng the info requested .. not avoind to do. Otherwise we can't produce valid suggestion – ScaisEdge Nov 29 '18 at 07:56
  • I posted my question with the indication of the cause and how to fix it. So i know it already. But i said both solutions don't match with my need because i can't have only the same columns in the GROUP BY and in the SELECT. Whatever my data structure is, the question is still : how to have MORE fields that the one in the GROUP_BY. My structure is basic : 2 tables and a joint table. It's a basic structure. And my expectation is to get ALL the columns. So is there a solution with the GROUP_CONCAT & GROUP_BY or is there an alternative with other MySQL method ? – Coool6 Nov 29 '18 at 08:07