CODEWITHSUNDEEP
mysqlsql-server
Boolangery
Boolangery

Reputation: 39

Complex SQL query with JOIN and GROUP BY on 4 tables

Here is my table structure (simplified) :

'Video' table contain a list of all videos.

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

'Channel' table contains all possible channels. It's a many to many relation with 'video' using 'video_channel' table.

channel
+----+-------+
| id | title |
+----+-------+

video_channel
+----+----------+------------+
| id | video_id | channel_id |
+----+----------+------------+

'Thumb' table contains several thumbs for each video :

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

What i need to get is :

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

I managed to get this :

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

with this query :

SELECT channel.title, COUNT(*) 
FROM video 
INNER JOIN video_channel ON video_channel.video_id=video.id
INNER JOIN channel ON video_channel.channel_id=channel.id
GROUP BY video_channel.channel_id
ORDER BY COUNT(*) DESC

I use MySql

Upvotes: 0

Views: 1699

Answers (3)

Boolangery
Boolangery

Reputation: 39

Thank you both, I finnaly managed to get what I want with this query :

SELECT c.title, COUNT(*),
(
    SELECT thumb.link
    FROM video
    INNER JOIN video_channel ON video_channel.video_id=video.id
    INNER JOIN thumb ON thumb.video_id=video_channel.video_id
    INNER JOIN channel ON channel.id=video_channel.channel_id
    WHERE video_channel.channel_id=c.id
    ORDER BY video.views DESC, thumb.id ASC
    LIMIT 1
) AS thumb_link
FROM channel c
INNER JOIN video_channel ON video_channel.channel_id=c.id
INNER JOIN video ON video.id=video_channel.video_id
GROUP BY video_channel.channel_id
ORDER BY COUNT(*) DESC

Upvotes: 1

mrcheshire
mrcheshire

Reputation: 535

The query to get the thumbnail for the most viewed video in a channel would look vaguely like this, right?

SELECT thumb.link FROM channel 
INNER JOIN video ON channel.id = video.channel_id
INNER JOIN thumb ON video.id = thumb.video_id
ORDER BY video.views DESC
LIMIT 1

It seems to me like the easiest way to do this is with a sub-query, so you can probably get the answer you want if you join your query with the one above, which should look something like this:

SELECT channel.title, COUNT(*), 
(SELECT thumb.link FROM channel1 
INNER JOIN video1 ON channel1.id = video1.channel_id
INNER JOIN thumb ON video1.id = thumb.video_id
WHERE video1.id = video.id
ORDER BY video1.views DESC
LIMIT 1) AS thumb.link
FROM video 
INNER JOIN video_channel ON video_channel.video_id=video.id
INNER JOIN channel ON video_channel.channel_id=channel.id
GROUP BY video_channel.channel_id
ORDER BY COUNT(*) DESC

But I'll admit that it usually takes me a bit of fiddling to get the subquery syntax right. I rarely get it on the first try.

Upvotes: 0

underscore_d
underscore_d

Reputation: 6792

Seems like you need a correlated subquery.

Assuming SQL Server, which is my original dialect, you could do something like this:

select
    channel.title,
    count(video_channel.video_id),
    _mostViewedThumb.link
from
    video_channel -- count
    inner join channel on -- title
        video_channel.channel_id = channel.id
    cross apply ( -- most viewed
        select top 1
            thumb.link
        from
            thumb
            inner join video on -- for order
                thumb.video_id = video.id
        where
            video_channel.video_id = thumb.video_id
        order by
            video.views desc
    ) as _mostViewedThumb
group by
    channel.title;

Upvotes: 1

Related Questions