0

Hey please I need help with a MYSQL query if you could please read on ill include almost everything you need.

Note: I have read multiple answers before I post but i'm not sure if these answers sort my issue.

The Query i'm using:

SELECT
   t1.name AS product_name,
   t1.description AS product_description,
   GROUP_CONCAT(t2.name) AS category
   FROM cdxc_product_description t1 INNER JOIN
   cdxc_product_to_category t3
   ON t3.product_id = t1.product_id INNER JOIN
   cdxc_category_description t2
  ON t2.category_id = t3.category_id
  GROUP BY t1.name, t1.description;

The result I get:

product_name  |   product_description |  product_category
-----------------------------------------------------------
 NAME 1       |     Description 1     |     Cat1,Cat2,Cat5

 NAME 2       |     Description 2     |     Cat7,Cat2,Cat1

Is there any way I could get a result like this?

product_name  |   description |  category | category2 | category3
-----------------------------------------------------------
 NAME 1       | Description 1 | Cat1      |   Cat2    |    Cat5

 NAME 2       | Description 2 | Cat7      |   Cat2    |    Cat1

PLEASE NOTE: I do not want to make sql generate anything dynamically as I know it's not possible, i just want you to point me to the right direction to get the result mentioned done either via a totally different query with a different approach or by just a modification to my query.

jukerok
  • 337
  • 2
  • 4
  • 10
  • For me it produces the latter behaviour - which is the expected behaviour for group_concat(). No rdbms can create fields dynamically, I do not understand how you would get the first result. Unless you mixed up the expected and actual results in your question. – Shadow Jun 19 '17 at 20:43
  • If you mixed up the expected and actual results in the question, then it is a duplicate question... – Shadow Jun 19 '17 at 20:47
  • i have updated the question because i saw your comment, thank you for trying to help – jukerok Jun 19 '17 at 20:49
  • and please if it's a duplicate question please include the link so i can use it – jukerok Jun 19 '17 at 20:49
  • I still don't get your question. Group_concat() produces the output as described under "Is there any way I could get a result like this?" – Shadow Jun 19 '17 at 20:51
  • ok let me explain it easily, forget about group_concat() please, what would you do to get the result i am aiming for (in the question) – jukerok Jun 19 '17 at 20:56
  • You still don't get it! Group_concat produces the result that you describe in your question. – Shadow Jun 19 '17 at 20:57
  • @Shadow i get the following result Cat1,Cat2,Cat5 under the category field, while i want to get each one in a separate column (not dynamically as i will set them myself) I have tried using offset but that didn't work so what do you think i should do? – jukerok Jun 19 '17 at 21:00
  • OK, this way it is clear now. Which in turn means that the question is a duplicate. – Shadow Jun 19 '17 at 21:00

0 Answers0