MariaDB 10.4

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table tasks (id int, parent_id varchar(255), title varchar(255))
select 4 id, "#" parent_id, "LEVEL 2" title union all
select 5   , "4", "discovered check (level 2)" union all
select 6   , "4", "windmill (level 2)"              union all
select 7   , "#", "LEVEL 3"              union all
select 8   , "7", "discovered check - level 3"              union all
select 9   , "7", "windmill - level 3"              union all
select 10   , "#", "LEVEL 1"           union all
select 11   , "10", "discovered check - winning a piece (level 1)"              union all
select 12   , "10", "discovered check - mate in one (level 1)"              ;
select * from tasks;


id
parent_id
title


4
#
LEVEL 2

5
4
discovered check (level 2)

6
4
windmill (level 2)

7
#
LEVEL 3

8
7
discovered check - level 3

9
7
windmill - level 3

10
#
LEVEL 1

11
10
discovered check - winning a piece (level 1)

12
10
discovered check - mate in one (level 1)


create table puzzles (id int, task_id int)
select  1 id, 5 task_id union all
select  2 id, 6 task_id union all
select  3 id, 6 task_id union all
select  4 id, 11 task_id union all
select  5 id, 12 task_id union all
select  4 id, 9 task_id union all
select  5 id, 9 task_id union all
select  2 id, 8 task_id;
select * from puzzles;


id
task_id


1
5

2
6

3
6

4
11

5
12

4
9

5
9

2
8


select
    id as task_id,
    title as title,
    (
        select p.id
        from puzzles p
        where p.task_id = t.id
        order by rand()
        limit 1
    ) as puzzle_id
from tasks t
where parent_id in (select id from tasks where parent_id = '#')
order by task_id;


task_id
title
puzzle_id


5
discovered check (level 2)
1

6
windmill (level 2)
3

8
discovered check - level 3
2

9
windmill - level 3
4

11
discovered check - winning a piece (level 1)
4

12
discovered check - mate in one (level 1)
5


WITH cte AS ( SELECT tasks.id task_id, 
                     tasks.title title, 
                     puzzles.id, 
                     row_number() over (partition by tasks.id order by rand()) rn
              FROM tasks
              JOIN puzzles ON tasks.id = puzzles.task_id 
              )
SELECT task_id, id, title
FROM cte
WHERE rn = 1;


task_id
id
title


5
1
discovered check (level 2)

6
3
windmill (level 2)

8
2
discovered check - level 3

9
4
windmill - level 3

11
4
discovered check - winning a piece (level 1)

12
5
discovered check - mate in one (level 1)

id	parent_id	title
4	#	LEVEL 2
5	4	discovered check (level 2)
6	4	windmill (level 2)
7	#	LEVEL 3
8	7	discovered check - level 3
9	7	windmill - level 3
10	#	LEVEL 1
11	10	discovered check - winning a piece (level 1)
12	10	discovered check - mate in one (level 1)