Welcome to the Treehouse Community
The Treehouse Community is a meeting place for developers, designers, and programmers of all backgrounds and skill levels to get support. Collaborate here on code errors or bugs that you need feedback on, or asking for an extra set of eyes on your latest project. Join thousands of Treehouse students and alumni in the community today. (Note: Only Treehouse students can comment or ask questions, but non-students are welcome to browse our conversations.)
Looking to learn something new?
Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and a supportive community. Start your free trial today.
Greg Kaleka39,019 Points
The Solution Provided Doesn't Work if Teachers Teach More Than One Grade
I think the solution isn't correct. The question is "Which teachers teach only students in 8th grade?"
The solution in the video is:
SELECT DISTINCT t.id, t.FIRST_NAME, t.LAST_NAME, s.GRADE FROM TEACHERS AS t JOIN CLASSES AS c ON t.ID = c.TEACHER_ID JOIN SUBJECTS AS s ON c.SUBJECT_ID = s.ID WHERE GRADE = 8;
This will work if none of the teachers teach both 8th grade and another grade, but if that were to be the case, this query would still turn them up. Instead, you need a subquery that pulls the ids of teachers who teach non-8th grade classes.
SELECT DISTINCT t.ID, t.FIRST_NAME, t.LAST_NAME, s.GRADE FROM TEACHERS AS t JOIN CLASSES AS c ON t.ID = c.TEACHER_ID JOIN SUBJECTS AS s ON c.SUBJECT_ID = s.ID WHERE GRADE = 8 AND t.ID NOT IN ( SELECT distinct t.id FROM TEACHERS AS t JOIN CLASSES AS c ON t.ID = c.TEACHER_ID JOIN SUBJECTS AS s ON c.SUBJECT_ID = s.ID WHERE GRADE != 8);
You can test these two by running an insert query to produce the edge case I'm talking about:
INSERT INTO CLASSES VALUES(99999, 22, 4, 374, 19);
That's a class with a non-8th grade subject and a teacher that's in the results from the first query. If you run the first query again, it's unchanged. If you run the second one, it correctly filters out the teacher with an ID of 374, since he now teaches a non-8th grade class.
Thomas Nilsen14,957 Points
To avoid duplicate code in your example above, can use a CTE (Common Table Expression) like this:
-- Which teachers teach only students in 8th grade? ;with DATA as ( SELECT DISTINCT t.ID, t.FIRST_NAME, t.LAST_NAME, s.GRADE FROM TEACHERS AS t JOIN CLASSES AS c ON t.ID = c.TEACHER_ID JOIN SUBJECTS AS s ON c.SUBJECT_ID = s.ID ) SELECT * FROM DATA WHERE GRADE = 8 AND ID NOT IN (SELECT ID FROM DATA WHERE GRADE != 8)
Thomas Nilsen14,957 Points
You are correct.
However. In the video he says at the end "...And that's all the teachers that teach 8th grade.", which is also correct.
There is a mismatch between the question and what is shown in the video.
Carlos Zuluaga15,126 Points
Is possible to solve this exercise using RIGHT JOIN? Unfortunately SQL Playground doesn't support it.
SELECT TEACHERS.FIRST_NAME, TEACHERS.LAST_NAME, SUBJECTS.GRADE FROM TEACHERS INNER JOIN CLASSES ON TEACHERS.ID = CLASSES.TEACHER_ID RIGHT JOIN SUBJECTS ON CLASSES.SUBJECT_ID = SUBJECTS.ID WHERE SUBJECTS.GRADE = 8;
Could the above code ever work?
I personally don't like using JOIN unless I need to return information from mroe than 1 table, and for this query I just need to return the information from the Teachers table, so prefer to use this
SELECT * from TEACHERS WHERE ID IN(SELECT TEACHER_ID FROM CLASSES WHERE SUBJECT_ID IN(SELECT ID FROM SUBJECTS WHERE GRADE=8));```