使用join从一个MYSQL表中选择随机行

Question

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;

Answer 1

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()