## 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.

# 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
```

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
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
```

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.

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
AND ID NOT IN (SELECT ID FROM DATA WHERE GRADE != 8)
```

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

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!

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.

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

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
```SELECT * from TEACHERS WHERE ID IN(SELECT TEACHER_ID FROM CLASSES WHERE SUBJECT_ID IN(SELECT ID FROM SUBJECTS WHERE GRADE=8));```