MySql Subquery IN returns the first result, not all results

Question

I have the following query:

SELECT TrackID, Name, Artist, Album, TotalTime, TrackNumber, TrackCount
FROM tracks
WHERE TrackID IN (SELECT Tracks FROM playlists
                  WHERE ID='f8ad830c-e2d6-11e1-99d7-00132094'
                  LIMIT 1)
ORDER BY Artist, Album, TrackNumber

Executing that returns the first row from the 'tracks' table to contain any of the results returned from the subquery. As of now, the Subquery returns:

41646,41696,41698,41700,41702,41704,41706,41708,41710,41712

When I put the above value returned from the subquery into the original query, in place of the subquery, it returns all the rows I would expect.

Why does the subquery limit the results returned to the first result, but replacing the subquery with the result returned (when ran in PHPMyAdmin) returns all of them?

This is happening, most probably because, your `Tracks` is being selected as a single string of text, instead of comma delimited numbers like this: `'41646,41696,41698,41700,41702,41704,41706,41708,41710,41712'` — hjpotter92, Oct 13 '12 at 22:37

score 2 · Answer 1 · answered Oct 14 '12 at 00:40

2

SELECT TrackID, Name, Artist, Album, TotalTime, TrackNumber, TrackCount 
  FROM tracks 
 WHERE FIND_IN_SET(TrackID
                  , (SELECT Tracks
                       FROM playlists 
                      WHERE ID='f8ad830c-e2d6-11e1-99d7-00132094' 
                      LIMIT 1
                    )
                  ) > 0
 ORDER BY Artist, Album, TrackNumber 
;

answered Oct 14 '12 at 00:40

Jon Armstrong - Xgc

264
2
3

I wouldn't leave it in this form. But this will likely be the easiest for you to understand, in the form closest to your attempt. – Jon Armstrong - Xgc Oct 14 '12 at 00:43
As you noted previously, breaking up the playlist into a list of one row per track is the more natural structure and will likely make more effective use of proper indexes, with a simple JOIN in most cases. – Jon Armstrong - Xgc Oct 14 '12 at 15:04

score 1 · Answer 2 · edited May 23 '17 at 11:56

1

You say limit 1 in your subquery. So, you get only 1 trackid and thus only one track.

You can either split the tracks up into multiple rows, so for one playlist you have as many rows as there are tracks, or you can split your tracks string into a temporary table. Look at MySQL: Split comma separated list into multiple rows for example or search for "split string into rows"

edited May 23 '17 at 11:56

Community

1
1

answered Oct 13 '12 at 22:08

Olaf Dietsche

72,253
8
102
198

There is a single row, with a comma deliminated list. The single row contains the comma deliminated list. But, I also tried removing the LIMIT 1 and the same result happened. – Nicholas Yost Oct 13 '12 at 22:09
So `tracks` has all `trackid`s seperated by comma? – Olaf Dietsche Oct 13 '12 at 22:12
Yes. It's literally the string I put above with the 5 digit numbers. – Nicholas Yost Oct 13 '12 at 22:28

MySql Subquery IN returns the first result, not all results

2 Answers2