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 trialGreg Kaleka
39,021 PointsMore Subqueries!
Here's an alternative solution that uses subqueries.
SELECT FIRST_NAME, LAST_NAME FROM TEACHERS WHERE ID IN (
SELECT TEACHER_ID FROM CLASSES WHERE SUBJECT_ID IN (
SELECT ID FROM SUBJECTS WHERE NAME = "Science" AND GRADE = 7
)
);
4 Answers
Göktuğ BATI
Python Web Development Techdegree Student 6,287 Pointsit can be solved without using subqueries or joins
SELECT DISTINCT T.ID, T.FIRST_NAME, T.LAST_NAME FROM TEACHERS T, CLASSES C WHERE T.ID = C.TEACHER_ID AND C.SUBJECT_ID = 21
kevin hudson
Courses Plus Student 11,987 PointsSuppose you didn't know the subject ID from a list of 1 billion subjects that were not ordered and Science happened to be somewhere between 789,000,001 and 937,000,675?
Evgeni Vershinin
15,038 PointsI also found this aproach much easier for the last three videos, simply begin with selecting what we need then narrow it down to the specifics with WHERE IN subqueries later on. Teacher name -> teacher class -> teacher subject. It seems like such a more logical aproach then what he does in the videos
Harris Handoko
3,932 PointsJust to contribute a little bit, the Common Table Expressions (CTE) version with derived table method: Credit to the poster Thomas who showed this method for the previous video.
-- Which teacher teaches 7th grade science?
WITH DATA AS (
SELECT T.ID, T.FIRST_NAME, T.LAST_NAME, S.NAME AS SUBJECT_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 DISTINCT FIRST_NAME, LAST_NAME FROM DATA
WHERE SUBJECT_NAME LIKE "%SCIENCE%" AND GRADE = 7