I have a table, Cars,

CarID | Car
1       Taurus
2       Ranger
3       Charger

and CarColors with colors for each car.

ColorID | CarID | Color
1         1       White
2         1       Blue
3         2       Black
4         1       Red
5         2       Pink
6         3       Orange

I want to get a random two cars, and all colors for those cars. I tried

SELECT * FROM Cars C
  LEFT JOIN CarColors CC ON C.CarID = CC.CarID
ORDER BY RAND()
LIMIT 2

But this, understandably, returns two rows.

I tried a subquery, something like

SELECT * FROM Cars C
  LEFT JOIN CarColors CC ON C.CarID = CC.CarID
WHERE C.CarID IN (SELECT CarID FROM Cars
                   ORDER BY RAND()
                   LIMIT 2)

But MYSQL doesn't support LIMIT within subqueries.

When I try to search for this, I get answers for limiting the JOIN's results (like 1 color for each car)

CodePudding user response：

If your MySQL version support ROW_NUMBER window function, you can try this

SELECT * FROM 
(
  SELECT *,ROW_NUMBER() OVER(ORDER BY RAND()) rn 
  FROM Cars
) C LEFT JOIN CarColors CC ON C.CarID = CC.CarID
WHERE rn <= 2

sqlfiddle

Another way is you can just use a subquery to get random cars then do JOIN

SELECT * FROM 
(
  SELECT * 
  FROM Cars
  ORDER BY RAND()
  LIMIT 2
) C LEFT JOIN CarColors CC ON C.CarID = CC.CarID