0

I don't know whether it is possible in MySQL or not. If someone has tried it, will reduce my time and number of lines in the code.

I have rating table, which has the following field

enter image description here

I have reason table, which has the following fields,

enter image description here

Also i have mapping table called rating_reason_mapping for mapping rating table and reason table

enter image description here

And i have the below query to fetch the details of these tables

SELECT ratings.id AS rating_id, ratings.user_id, ratings.mdn, ratings.rating_value, ratings.rated_time, ratings.comment, reasons.reason
FROM ratings, reasons
JOIN rating_reason_mapping
WHERE rating_reason_mapping.rating_id = ratings.id
AND reasons.id = rating_reason_mapping.reason_id

Of course it will display the result like below,

enter image description here

But this is not the result i am expecting one , i don't want the repetation of all the rows instead i need the reasons in an array.For example you can see that rating_id 59 has repeated three times. All the values except the reasons are same for these three. I don't need this to be repeat in three times. I just need this in one time only. But i need all the reasons of the three rows. The reasons should be in an array.Like below

enter image description here

Of course the rating id 71,46,47 and 20 will have values in the above result

Is this possible in MySQL ???

Sakeer
  • 1,885
  • 3
  • 24
  • 43
  • Try by changing `SELECT ratings.id AS rating_id,...` to `SELECT DISTINCT ratings.id AS rating_id,...` – Sathvik Chinnu Aug 25 '16 at 12:32
  • Both of your answers did not solve my problem. @Ollie Jones : When i run like you have marked as duplicate (SELECT ratings.id AS rating_id, ratings.user_id, ratings.mdn, ratings.rating_value, ratings.rated_time, ratings.comment, GROUP_CONCAT( reasons.reason SEPARATOR ', ' ) FROM ratings, reasons JOIN rating_reason_mapping WHERE rating_reason_mapping.rating_id = ratings.id AND reasons.id = rating_reason_mapping.reason_id) I am getting the result only for rating Id 59. But what about the others rating id 71,46,47 and 20 ?? – Sakeer Aug 25 '16 at 12:51
  • 1
    Try this query `SELECT ratings.id AS rating_id, ratings.user_id, ratings.mdn, ratings.rating_value, ratings.rated_time, ratings.comment, GROUP_CONCAT(reasons.reason) FROM ratings, reasons JOIN rating_reason_mapping WHERE rating_reason_mapping.rating_id = ratings.id AND reasons.id = rating_reason_mapping.reason_id GROUP BY ratings.user_id` – Sathvik Chinnu Aug 25 '16 at 12:57
  • Wow great its worked. Some minor change GROUP BY rating_id worked. Thank you sathvik, thank you very much. You saved my lot of time – Sakeer Aug 25 '16 at 13:03
  • Yeah, I haven't seen the table much carefully. – Sathvik Chinnu Aug 25 '16 at 13:12

0 Answers0