mysql - Complex SQL query with JOIN and GROUP BY on 4 tables -

here table structure (simplified) :

'video' table contain list of videos.

video +----+-------+-------+ | id | title | views | +----+-------+-------+ 

'channel' table contains possible channels. it's many many relation 'video' using 'video_channel' table.

channel +----+-------+ | id | title | +----+-------+  video_channel +----+----------+------------+ | id | video_id | channel_id | +----+----------+------------+ 

'thumb' table contains several thumbs each video :

thumb +----+------+----------+ | id | link | video_id | +----+------+----------+ 

what need :

+---------------+-----------------+-------------------------------------------+ | channel.title | number of video | first thumb of viewed video |  |               | per channel     | channel                                   | +---------------+-----------------+-------------------------------------------+ 

i managed :

+---------------+-----------------+ | channel.title | number of video | +---------------+-----------------+ 

with query :

select channel.title, count(*)  video  inner join video_channel on video_channel.video_id=video.id inner join channel on video_channel.channel_id=channel.id group video_channel.channel_id order count(*) desc  

i use mysql

seems need correlated subquery.

assuming sql server, original dialect, this:

select     channel.title,     count(video_channel.video_id),     _mostviewedthumb.link     video_channel -- count     inner join channel on -- title         video_channel.channel_id = channel.id     cross apply ( -- viewed         select top 1             thumb.link                     thumb             inner join video on -- order                 thumb.video_id = video.id                     video_channel.video_id = thumb.video_id         order             video.views desc     ) _mostviewedthumb group     channel.title;