Afternoon folks,
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;
you could use an inner join
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()
LIMIT 1) cuttings on cuttings.track_id = tracks.track_id
WHERE tracks.active = '1'
ORDER BY RAND()