MySQL: Join three tables, comparing two values

Question

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

Table1:

LessonName        TeacherID
Maths              3
Biology            2
Biology            4
Geology            1

Table2:

Lesson    PricePerClass
Maths         200
Biology       100
Geology       150

Table3:

IDTeacher  TeacherName
1          Mike
2          John
3          Lauren
4          Julian

So far I've made this:

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 > (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 = "Mike");

And I don't know how to keep going because when I run it, an error appears.

My expected result would be something like:

IDTeacher   TotalRevenue
3           200

Thanks!

Answer 1

In your main query you must use a HAVING clause instead of a WHERE clause and also in the subquery fix your joins:

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 
having TotalRevenue > (
                        select sum(t2.PricePerClass)
                        from Table3 t3 
                        inner join Table1 as t1 on t1.TeacherId = t3.IDTeacher
                        inner join Table2 as t2 on t2.Lesson = t1.LessonName
                        where t3.TeacherName = "Mike"
                      );

See the demo.
Results:

| IDTeacher | TotalRevenue |
| --------- | ------------ |
| 3         | 200          |