I have had a good dig around and can't find the answer, so a good time to ask!
I'd like to select random rows from one table and then join into this random rows from another table where the ID that I have is the same. It would also be great to only select where I have an entry in the second table. I have tried all manner of sub-queries but am getting a bit lost. An inner join as read will do it but again with the randomness of it all!! Grrr...
SELECT tracks.track_id, cuttings.square_cutting, cuttings.cutting_2, cuttings.cutting_3, cuttings.blog_text FROM tbl_tracks tracks, (SELECT square_cutting, cutting_2, cutting_3, blog_text FROM tbl_cuttings WHERE track_id = tracks.track_id <-- wont find it, obviously!! ORDER BY RAND() LIMIT 1) cuttings WHERE tracks.active = '1' ORDER BY RAND()
Thanks in advance for any help.
So: I'd like random tracks showing track id -> with random cuttings, of which there can be many but I just want 1.
It would then be ideal to only show a result if there is a cutting associated with that track.
Hope that helps.
I'm now trying to go a step further with this and order this by a RAND() seed as I'm now having to add in pagination. Only problem is that its not giving me back the same random list due to a given seed. Any Ideas?
SELECT tracks.track_id, cuttings.square_cutting, cuttings.cutting_2, cuttings.cutting_3, cuttings.blog_text FROM tbl_tracks tracks INNER JOIN (SELECT track_id, square_cutting, cutting_2, cutting_3, blog_text FROM tbl_cuttings ORDER BY RAND()) cuttings ON tracks.track_id = cuttings.track_id WHERE tracks.active = '1' ORDER BY RAND(1) LIMIT 0,4;