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!
Advertisement
Answer
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 |