3

I have a nested subquery that selects a random AlbumID that the selected video is in (videos can be in multiple albums), and the outer query then returns the videos and album information based on that AlbumID.

The problem is that the query is returning mixed results; sometimes it gives me some of the videos from one album, sometimes it gives videos from multiple albums, sometimes it returns nothing.

The outer query works if I specify a specific AlbumID instead of the subquery, and the subquery by itself correctly returns 1 random AlbumID. But put together, it's giving me mixed results. What am I missing? Why is it returning varying amounts of rows, and multiple albums?

I've replicated the issue with test data, you can find the CREATE queries here: http://pastebin.com/raw.php?i=e6HaaSGK

The SELECT SQL:

SELECT  
    Videos_Demo.VideoID,
    VideosInAlbums_Demo.AlbumID
FROM 
    VideosInAlbums_Demo
    LEFT JOIN
        Videos_Demo
        ON Videos_Demo.VideoID = VideosInAlbums_Demo.VideoID
WHERE
    VideosInAlbums_Demo.AlbumID = (
                                    SELECT
                                        AlbumID
                                    FROM
                                        VideosInAlbums_Demo
                                    WHERE
                                        VideoID = '1'
                                    ORDER BY
                                        RAND()
                                    LIMIT 1
                                    )
Luke Shaheen
  • 4,262
  • 12
  • 52
  • 82
  • Weird. That makes no sense. One thing you could try is to join to the subquery and show the AlbumID it is returning in your select cause. Perhaps this could help you troubleshoot it. Otherwise you would probably have to post some records and results that are coming up funny. Hard to diagnose otherwise. – Tom Nov 13 '12 at 15:36
  • @Tom Glad that it's not something obvious I'm missing. I'll keep trying to diagnose it then, I'll try the join, thanks. – Luke Shaheen Nov 13 '12 at 15:37
  • @Tom I added `CREATE TABLE` SQL so you can replicate the issue, and I've modified the SQL to reflect the demo tables. I'm getting the same issue with this test data. – Luke Shaheen Nov 13 '12 at 16:10
  • @Tom Removing the `ORDER BY RAND()` works, so obviously the `RAND()` is what's causing the error. But now, why? – Luke Shaheen Nov 13 '12 at 16:34

1 Answers1

5

Try this. Moving the subquery to the JOIN seems to fix the problem. I think the problem has to do with having the subquery in the WHERE clause. I think that in the WHERE clause, the subquery and RAND function is being getting executed for each record. This is probably why the results are varying.

SELECT  a.AlbumID,
        Videos_Demo.VideoID,
        VideosInAlbums_Demo.AlbumID

FROM    VideosInAlbums_Demo

        LEFT JOIN Videos_Demo
        ON Videos_Demo.VideoID = VideosInAlbums_Demo.VideoID

        JOIN 
        (
            SELECT  AlbumID
            FROM    VideosInAlbums_Demo
            WHERE   VideoID = '1'
            ORDER BY RAND()
            LIMIT 1
        ) AS a ON VideosInAlbums_Demo.AlbumID = a.AlbumID
Tom
  • 6,593
  • 3
  • 21
  • 42
  • Works perfect, thank you! IF anyone has a technical explanation on why exactly the `RAND()` wasn't working in the `WHERE` clause, I'd love to hear it. – Luke Shaheen Nov 13 '12 at 17:00
  • I think that what was happening was that the subquery was being run for each record being returned. So a different AlbumID was being generated each time. That record would be returned depending on if it matched the currently generated AlbumID from the RAND function. So every time you ran it, you would get different results. – Tom Nov 13 '12 at 17:21