title: MySQL: Join three tables, comparing two values
body: <p>First of all, I'm an amateur on SQL. Here it is the example. From this three tables I would like to know who are the teachers that make more money than Mike</p>
  <p>Table1:</p>
  <pre><code>LessonName        TeacherID
  Maths              3
  Biology            2
  Biology            4
  Geology            1
  </code></pre>
  <p>Table2:</p>
  <pre><code>Lesson    PricePerClass
  Maths         200
  Biology       100
  Geology       150
  </code></pre>
  <p>Table3:</p>
  <pre><code>IDTeacher  TeacherName
  1          Mike
  2          John
  3          Lauren
  4          Julian
  </code></pre>
  <p>So far I've made this:</p>
  <pre><code>select t3.IDTeacher, sum(t2.PricePerClass) TotalRevenue
  from Table3 t3 
  inner join Table1 as t1 on t1.TeacherId = t3.IDTeacher
  inner join Table2 as t2 on t2.Lesson = t1.LessonName
  group by t3.IDTeacher 
  where TotalRevenue &gt; (select TotalRevenue
                       from Table2 as t2
                       inner join Table1 as t1 on t2.Lesson = t3.LessonName
                       inner join Table3 as t3 on t3.IDTeacher = t1.TeacherID
                       where t3.TeacherName = &quot;Mike&quot;);
  </code></pre>
  <p>And I don't know how to keep going because when I run it, an error appears.</p>
  <p>My expected result would be something like:</p>
  <pre><code>IDTeacher   TotalRevenue
  3           200
  </code></pre>
  <p>Thanks!</p>