CODEWITHSUNDEEP
mysqlsubquerylimit
Ripptor
Ripptor

Reputation: 330

Limit subquery without limiting entire query

I am trying to implement a top-result search for a playlist. The tables are like so:

Playlist: id, title
Assignments: id, youtube_id, position
Videos: youtube_id, title

So joining them is simply: Playlist =(id)= Assignments =(youtube_id)= Videos

I want to find the first playlist on a title match (Titles are not unique) and match it to each of it's videos, to bring back a list of videos in that playlist. I tried the following query:

SELECT * 
FROM ( 
SELECT id, title, position, youtube_id
FROM playlists p 
JOIN playlist_assignments pa 
USING(id) 
WHERE 1 and title = 'My Top Videos'
LIMIT 1
)d 
JOIN videos v 
USING (youtube_id)
ORDER BY position ASC

Unfortunately, the query will only return 1 row, the playlist matched with just the first video. How can I limit the subquery to just the top result, but still have a final result of a multiple row answer?

Upvotes: 0

Views: 1962

Answers (3)

Abhay
Abhay

Reputation: 6645

The issue with your query is that the sub-query is doing a LIMIT 1, which means that though you retrieved a single playlist but then you also retrieved a single youtube_id. Then when did you ran a JOIN with videos on that single youtube_id, it gives you a single record.

The fix might be to SELECT only from playlists table in the sub-query and move the JOIN for playlist_assignments outside together with the videos join. Something like:

SELECT *
FROM (
    SELECT id, title, position, youtube_id
    FROM playlists p 
    WHERE title = 'My Top Videos'
    LIMIT 1
) d 
JOIN playlist_assignments pa USING (id)
JOIN videos v 
USING (youtube_id)
ORDER BY position ASC;

My suggested query is:

SELECT `p`.`id` `playlist_id`, `p`.`title` `playlist_title`, `v`.`youtube_id` `youtube_id`, `v`.`title` `youtube_title`, `pa`.`position` 
FROM `playlists` `p`
INNER JOIN `playlist_assignments` `pa` ON `p`.`id` = `pa`.`id`
INNER JOIN `videos` `v` ON `pa`.`youtube_id` = `v`.`youtube_id`
WHERE `p`.`title` = 'My Top Videos'
ORDER BY `pa`.`position`;

Hope this helps. Let me know if it worked or if it didn't.

Upvotes: 2

Damp
Damp

Reputation: 3348

The way your query is written, you limit the results from playlist_assignments to 1. If I understand correctly, you just want one playlist but all the videos from that playlist. In that case, modifying your query this way should work

SELECT * 
FROM ( 
SELECT id, title
FROM playlists p 
WHERE 1 and title = 'My Top Videos'
LIMIT 1
)d 
JOIN playlist_assignments pa 
USING(id) 
JOIN videos v 
USING (youtube_id)
ORDER BY position ASC

Upvotes: 0

Guile
Guile

Reputation: 1494

I have not tested, but the idea is to try to use an aggregate MIN in your subquery to get min playlist id for each youtube videos you have, and then join it to the playlist table

SELECT p.*, v.*
FROM
  (SELECT MIN(id) AS id, youtube_id
  FROM playlists p 
  JOIN playlist_assignments pa USING(id) 
  WHERE Title = 'My Top Videos'
  GROUP BY youtube_id) AS firstPlaylistByVideo
JOIN playlists p ON p.id = firstPlaylistByVideo.id
JOIN videos v USING (youtube_id)
ORDER BY position ASC

Upvotes: 0

Related Questions