How to extract a random row from a child table?

Question

MySQL

I have a parent table and a child table.
Parent table (tasks)
id parent_id
4 # <- Main (parent theme) Уровень 2
5 4 <- child открытый шах
6 4 <- child мельница
In the interface it looks like this

And there is a child (puzzles) table of tasks - tasks can only be in child topics

id puzzle_id task_id
1 52 5
2 61 6
5 25 6
6 70 5

I need to extract a random puzzle (puzzles) from a second level topic (tasks), it can belong to any second level topic - either мельница, or открытый шах. But I have a problem with compiling such a query. There are many such main topics and child topics in the database, so you can’t bind to id. Can you please tell me what it looks like?

Answer 1

Something like

select 
  puzzles.* 
from tasks 
  inner join puzzles 
    on tasks.parent_id = :id 
      and tasks.id = puzzles.task_id 
order by rand()
limit 1

if there are many lines suitable for selection by task_id, then see the optimization here: https://habr.com/ru/post/154905/

Answer 2

rand() limit 1 in a subquery, see optimization there