Select row where row number is equal to some value from other table

I have two tables: game and tasks

Game looks like this:

| step | manualTaskCounter | autoTaskCounter | (and other)
----------------------------------------------------------
|  1   |      3            | 1               | ...
----------------------------------------------------------

Tasks looks like this:

| id | taskType | taskContent |
-------------------------------
|  1 |    M     | abc         |
|  2 |    M     | cde         |
|  3 |    A     | efg         |
|  4 |    M     | jpq         |

Since tasks holds both, manual (with M taskType) and automatic (A) tasks I want to select. My API holds two variables: mTaskCounter and aTaskCounter. for example if mTaskCounter = 3 I want to select 3rd row of type manualTask from tasks. Since it is in fact row with id = 4 I can not use id in WHERE clause.

What I already achieved is:

SELECT
    id,
    taskType,
    taskContent,
    (@row:=@row + 1) as rowNumber,
    g.manualTaskCounter as mTaskCounter
FROM
    tasks t,
    (SELECT @ROW:=0) AS r,
    (SELECT manualTaskCounter FROM game) AS g
WHERE
    g.manualTaskCounter = rowNumber

This says "unknown column 'rowNumber' in where clause

I also tried to use LEFT JOIN:

SELECT
    id,
    taskType,
    taskContent,
    (@row:=@row + 1) as rowNumber,
    g.manualTaskCounter as mTaskCounter
FROM
    tasks t,
    (SELECT @ROW:=0) AS r
LEFT JOIN
    `game` g ON g.manualTaskCounter = rowNumber

Same result. It's been a while since I used mysql everyday and dont know how to fix it. I also think to make two tables - manualTasks and autoTasks instead of tasks so it qould solve the problem by common select taskContent from autoTasks a LEFT JOIN game ON a.id = game.autoTaskCounter

Solution

For approaching your goal, first you will need to make derived tables for both manual and automatic tasks. Next queries will made those tables adding up the row number too:

Table With Manual Tasks

SELECT
    t.id,
    t.taskType,
    t.taskContent,
    (@row_num := @row_num + 1) AS rowNum
FROM
    tasks AS t
CROSS JOIN
    (SELECT @row_num := 0) AS r
WHERE
    taskType = 'M'

Table With Automatic Tasks

SELECT
    t.id,
    t.taskType,
    t.taskContent,
    (@row_num := @row_num + 1) AS rowNum
FROM
    tasks AS t
CROSS JOIN
    (SELECT @row_num := 0) AS r
WHERE
    taskType = 'A'

Now, all you need to do is join those derived tables with the game table on the adequate columns:

Select manual task number X using the manualTaskCounter field

SELECT
    mTasks.*
FROM
    game AS g
INNER JOIN
    ( SELECT
          t.id,
          t.taskType,
          t.taskContent,
          (@row_num := @row_num + 1) AS rowNum
      FROM
          tasks AS t
      CROSS JOIN
          (SELECT @row_num := 0) AS r
      WHERE
           taskType = 'M' ) AS mTasks ON mTasks.rowNum = g.manualTaskCounter

Select automatic task number X using the autoTaskCounter field

SELECT
    aTasks.*
FROM
    game AS g
INNER JOIN
    ( SELECT
          t.id,
          t.taskType,
          t.taskContent,
          (@row_num := @row_num + 1) AS rowNum
      FROM
          tasks AS t
      CROSS JOIN
          (SELECT @row_num := 0) AS r
      WHERE
           taskType = 'A' ) AS aTasks ON aTasks.rowNum = g.autoTaskCounter

Check the next online example:

DB Fiddle Example