0

I have this main query here:

SELECT tbl_vehicles.id, tbl_vehicles.Model, tbl_vehicles.Brand, tbl_vehicles.isAutoTrans, tbl_vehicles.Seats, tbl_vehicles.Price, tbl_brands.BrandName
FROM tbl_vehicles
INNER JOIN tbl_brands
ON tbl_vehicles.Brand=tbl_brands.id
ORDER BY id

and i need to get 9 random results of the above query. I found this answer here which is exactly what I need.

SELECT * FROM tbl_vehicles AS r1 JOIN (SELECT CEIL(RAND() * (SELECT MAX(id) FROM tbl_users)) AS id) AS r2 WHERE r1.id >= r2.id ORDER BY r1.id ASC LIMIT 9

How can I put them together and keep my inner join?

Obviously my structure is wrong but why?

SELECT * FROM tbl_vehicles INNER JOIN tbl_brands ON tbl_vehicles.Brand=tbl_brands.id ORDER BY id AS r1 JOIN (SELECT CEIL(RAND() * (SELECT MAX(id) FROM tbl_users)) AS id) AS r2 WHERE r1.id >= r2.id ORDER BY r1.id ASC LIMIT 9
Tomalak
  • 332,285
  • 67
  • 532
  • 628
Bobys
  • 677
  • 1
  • 14
  • 37

1 Answers1

1

i need to get 9 random results of the above query

Shouldn't this be enough?

SELECT 
  v.id, v.Model, v.Brand, v.isAutoTrans, v.Seats, v.Price, b.BrandName
FROM
  tbl_vehicles v
  INNER JOIN tbl_brands v ON v.Brand = b.id
ORDER BY
  RAND()
LIMIT 9;
Tomalak
  • 332,285
  • 67
  • 532
  • 628
  • That avoids repeating rows? I believe the aim of Riedsio answer is to avoid that. I might be wrong. – Bobys Jun 13 '21 at 09:36
  • @Bobys There are no repeating rows, so there is nothing to avoid. `ORDER BY RAND()` simply shuffles the result set. – Tomalak Jun 13 '21 at 09:37
  • You are correct Tomalak, I tested a few times no repeated rows. Thanks for your tip! I'll accept your answer! – Bobys Jun 13 '21 at 10:01
  • 1
    @Bobys There is nothing to test. An `ORDER BY` statement *cannot* duplicate any rows. When the base query has no duplicates, then ordering things differently will not magically produce duplicates. The other answer you found solves a completely different task - picking N random rows, but *spaced with even gaps*, which is a lot more specific than your requirement here. Always read past the top-voted/accepted answer in SO threads - `ORDER BY RAND()` was the #2 answer in that other thread, you could have seen it. – Tomalak Jun 13 '21 at 10:47
  • Thanks for the detailed explanation Tomalak. I totally got your point. I though RAND() goes N times through the table and picks 1 random row. Hence, my worry regarding duplicate rows. Obviously I was wrong. Thanks again man! – Bobys Jun 13 '21 at 11:55
  • 1
    @Bobys No, `RAND()` only generates random numbers. If you create one random number for each row, and then sort all rows ascending by that value, you will get randomly sorted rows. :) – Tomalak Jun 13 '21 at 12:41