title: why can't join 2 tables with different num of rows on id?
body: <p>I need to divide time from table A by table B to find relative completion time in % of a task.
  The time column shows the time student needed to finish a task.</p>
  <p>if the total time it took was 2 min, and the attempt time was 1 min, then the relative time is 50%</p>
  <p>One table has 30 rows - it' s a derived table that shows total time,</p>
  <p>another 59 rows - time it took for each step without summation,</p>
  <p>common field is named st_id (step_id).</p>
  <p>The first table A :</p>
  <pre><code>+-------+---------+
  | st_id | time    |
  +-------+---------+
  | 10    | 0:00:28 |
  | 11    | 0:01:51 |
  | 12    | 0:10:09 |
  | 13    | 0:03:44 |
  | 14    | 0:16:44 |
  | 15    | 0:19:12 |
  | 16    | 0:03:43 |
  | 17    | 0:07:56 |
  | 18    | 0:06:19 |
  | 19    | 0:31:14 |
  | 20    | 0:11:50 |
  | 81    | 0:14:12 |
  | 82    | 0:18:59 |
  | 83    | 0:30:24 |
  | 84    | 0:11:50 |
  | 85    | 0:40:35 |
  | 86    | 1:28:57 |
  | 87    | 0:24:10 |
  | 88    | 0:51:53 |
  | 100   | 0:05:31 |
  | 101   | 0:13:41 |
  | 103   | 0:11:50 |
  | 104   | 0:22:18 |
  | 105   | 0:35:18 |
  | 106   | 0:55:02 |
  | 107   | 0:42:02 |
  | 108   | 0:08:52 |
  | 109   | 1:07:07 |
  | 110   | 0:11:50 |
  | 111   | 0:11:55 |
  +-------+---------+
  </code></pre>
  <p>another table has 59 rows - table B:</p>
  <pre><code>+------------+---------+---------------+
  | student_id | st_id   | time          |
  +------------+---------+---------------+
  | 59         | 10      | 0:00:28       |
  | 59         | 11      | 0:01:11       |
  | 59         | 12      | 0:06:09       |
  | 59         | 13      | 0:02:24       |
  | 59         | 14      | 0:00:05       |
  | 59         | 14      | 0:00:04       |
  | 59         | 14      | 0:00:20       |
  | 59         | 14      | 0:00:23       |
  | 59         | 14      | 0:09:42       |
  | 59         | 14      | 0:00:10       |
  | 59         | 15      | 0:11:44       |
  | 59         | 15      | 0:00:08       |
  | 59         | 16      | 0:02:23       |
  | 59         | 17      | 0:04:53       |
  | 59         | 17      | 0:00:23       |
  | 59         | 18      | 0:00:07       |
  | 59         | 18      | 0:03:19       |
  | 59         | 18      | 0:00:53       |
  | 59         | 19      | 0:00:07       |
  | 59         | 19      | 0:00:29       |
  | 59         | 19      | 0:00:10       |
  | 59         | 19      | 0:00:06       |
  | 59         | 19      | 0:00:04       |
  | 59         | 19      | 0:00:07       |
  | 59         | 19      | 0:00:24       |
  | 59         | 19      | 0:00:08       |
  | 59         | 19      | 0:00:08       |
  | 59         | 19      | 0:02:11       |
  | 59         | 19      | 0:00:10       |
  | 59         | 19      | 0:15:50       |
  | 59         | 20      | 0:07:10       |
  | 59         | 81      | 0:08:52       |
  | 59         | 82      | 0:11:31       |
  | 59         | 82      | 0:00:08       |
  | 59         | 83      | 0:00:04       |
  | 59         | 83      | 0:00:08       |
  | 59         | 83      | 0:00:51       |
  | 59         | 83      | 0:00:15       |
  | 59         | 83      | 0:17:46       |
  | 59         | 84      | 0:07:10       |
  | 59         | 85      | 0:24:35       |
  | 59         | 86      | 0:53:14       |
  | 59         | 86      | 0:00:23       |
  | 59         | 87      | 0:07:10       |
  | 59         | 87      | 0:07:40       |
  | 59         | 88      | 0:31:13       |
  | 59         | 100     | 0:03:31       |
  | 59         | 101     | 0:08:21       |
  | 59         | 103     | 0:07:10       |
  | 59         | 104     | 0:13:38       |
  | 59         | 105     | 0:21:18       |
  | 59         | 106     | 0:33:02       |
  | 59         | 107     | 0:07:10       |
  | 59         | 107     | 0:18:12       |
  | 59         | 108     | 0:05:32       |
  | 59         | 109     | 0:40:27       |
  | 59         | 110     | 0:07:10       |
  | 59         | 111     | 0:07:15       |
  +------------+---------+---------------+
  </code></pre>
  <p>I tried to inner join by step_id, but it gives me the error:
  &quot;derived table has diff num of rows&quot;</p>
  <p>But i m only trying to join those that exist in both tables? that's how inner join works?</p>
  <p>I have both tables inside with clause, they pull attempt time data for student_id = 59:</p>
  <pre><code>with  step_helper(st_id, t) as (  
  
  select  step_id, sec_to_time(sum(t)) as total_time from (select  step_id,
  case when (submission_time - attempt_time) &gt; 3600 then sec_to_time((select * from s1))
  else sec_to_time((submission_time - attempt_time)) end as t
  from step_student  where student_id = 59)k
  group by step_id),
  
  time_attempt(st_id, t) as (
  select student.student_id, step.step_id,
  case when (submission_time - attempt_time) &gt; 3600 then sec_to_time((select * from s1))
  else sec_to_time((submission_time - attempt_time)) end as Время_попытки   
  from step_student  inner join student on student.student_id = step_student.student_id 
  inner join step on step.step_id = step_student.step_id where student.student_id = 59)
  </code></pre>
  <p>this inner joining the 2 tables above does not work:</p>
  <pre><code>SELECT *FROM  time_attempt INNER JOIN step_helper ON time_attempt.st_id = step_helper.st_id
  </code></pre>
  <blockquote>
  <p>In definition of view, derived table or common table expression,
  SELECT list and column names list have different column counts</p>
  </blockquote>