Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

I have the following problem I have 3 table (all of the are used utf8 / utf8_general_ci encoding) movies, channels, i also have 3 table movie_channels which

is just a combination of the other two with just 2 fields: movie_id,channel_id here is my channels table (code,name)

'1', 'ОРТ'
'2', 'ТК Спорт'
'3', 'ТК ТНВ'
'4', 'НТВ'
'5', 'НТВ+'
'6', 'TSN'

here is my movie_channels table (movie _id, channel_id) channel_id references code field in channels table

'19', '2'
'19', '6'
'95', '1'
'95', '4'
'96', '1'
'96', '4'
'97', '1'
'97', '4'
'98', '1'
'98', '4'
'99', '1'
'99', '4'
'100', '1'
'100', '4'

don't mind quotes on id values. they are all ints of course, not chars, it's just pasting issue

for each movie i need to display comma separated list of channels i used mysql group_concat

select t.movie_id,( select group_concat(c.name) from 
movie_channels mc
join channels c  on mc.channel_id=c.code
where mc.movie_id = t.movie_id

order by code desc )as audio_channel from movies t 

but I dont like the order of concat for movie_id #19 i need the above sql to display TSN,ТК Спорт but it keeps returning me ТК Спорт,TSN . I tried to use order by code desc with no luck ,tried order by char_length(asc) with no success any ideas ?

share|improve this question

2 Answers 2

up vote 1 down vote accepted

If for every movie you want to list all the channels that are showing that movie, you need something like this:

SELECT
  mc.movie_id,
  GROUP_CONCAT(c.name) AS channels
FROM movie_channels AS mc
JOIN channels AS c
ON c.code = mc.channel_id
GROUP BY mc.movie_id
ORDER BY c.code DESC;
share|improve this answer

You can add a ORDER BY clause inside the GROUP_CONCAT() aggregate to adjust the ordering of the grouped string.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.
{"cc_re__cc_re__cc_by_sa_3_0": ["                  <li><a href=\"//area51.stackexchange.com\" title=\"proposing new sites in the Stack Exchange network\">Area 51</a></li>\r\n                        <li><a href=\"//careers.stackoverflow.com\">Stack Overflow Careers</a></li>\r\n                    \r\n                </ol>\r\n            </td>\r\n    </tr>\r\n</table>\r\n                </div>\r\n            </div>\r\n\r\n            <div id=\"copyright\">\r\n                site design / logo &#169; 2015 stack exchange inc; user contributions licensed under <a href=\"http://creativecommons.org/licenses/by-sa/3.0/\" rel=\"license\">cc by-sa 3.0</a> \r\n                with <a href=\"http://blog.stackoverflow.com/2009/06/attribution-required/\" rel=\"license\">attribution required</a>\r\n            </div>\r\n            <div id=\"svnrev\">\r\n                rev 2015.4.28.2538\r\n            </div>\r\n            \r\n        </div>\r\n    </div>\r\n    <noscript>\r\n        <div id=\"noscript-warning\">Stack Overflow works best with JavaScript enabled<img src=\"http://pixel.quantserve.com/pixel/p-c1rF4kxgLUzNc.gif\" alt=\"\" class=\"dno\"></div>\r\n    </nos"], "copyright__copyright__copyright": ["ONCAT(c.name) AS channels\nFROM movie_channels AS mc\nJOIN channels AS c\nON c.code = mc.channel_id\nGROUP BY mc.movie_id\nORDER BY c.code DESC;\nshare|improve this answer\n\nYou can add a ORDER BY clause inside the GROUP_CONCAT() aggregate to adjust the ordering of the grouped string.\n\nshare|improve this answer\n\nYour Answer\n\n\u00a0\ndiscard\n\nBy posting your answer, you agree to the privacy policy and terms of service.\n\nNot the answer you're looking for? Browse other questions tagged or ask your own question."]}