Welcome to the Treehouse Community
Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.
Looking to learn something new?
Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.
Start your free trial
Greg Kaleka
39,021 PointsThe 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.
4 Answers
Thomas Nilsen
14,957 PointsBTW;
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 Nilsen
14,957 PointsYou 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.
Greg Kaleka
39,021 PointsYeah it's a bit ambiguous, although even in the video, @0:16, Ben Deitch says "teach only students in 8th grade."
Carlos Zuluaga
21,184 PointsIs 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?
Matthew Pryor
Courses Plus Student 12,330 PointsI 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));```
Thomas Nilsen
14,957 PointsJoin is more often than not, a better choice than a subquery.
Greg Kaleka
39,021 PointsGreg Kaleka
39,021 PointsOoh cool. Didn't know about those. Thanks for the tip!
Carlos Zuluaga
21,184 PointsCarlos Zuluaga
21,184 PointsCool. Does it work like a function? Can I add parameters?
Very interesting. Especially to avoid SQL Injection.
Charlie Harcourt
8,046 PointsCharlie Harcourt
8,046 PointsThat's a cool little trick! I didn't know about it either!
Thanks!