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

Databases SQL Reporting by Example Day 1: Joining Tables 8th Grade Teachers

Greg Kaleka
Greg Kaleka
39,021 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.

4 Answers

Thomas Nilsen
Thomas Nilsen
14,957 Points

BTW;

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)
Greg Kaleka
Greg Kaleka
39,021 Points

Ooh cool. Didn't know about those. Thanks for the tip!

Carlos Zuluaga
Carlos Zuluaga
21,184 Points

Cool. Does it work like a function? Can I add parameters?

Very interesting. Especially to avoid SQL Injection.

That's a cool little trick! I didn't know about it either!

Thanks!

Thomas Nilsen
Thomas Nilsen
14,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.

Greg Kaleka
Greg Kaleka
39,021 Points

Yeah it's a bit ambiguous, although even in the video, @0:16, Ben Deitch says "teach only students in 8th grade."

Carlos Zuluaga
Carlos Zuluaga
21,184 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));```
Thomas Nilsen
Thomas Nilsen
14,957 Points

Join is more often than not, a better choice than a subquery.

SubQuery or Inner Join? Which is More Efficient?