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.

Databases SQL Reporting by Example Day 1: Joining Tables 7th Grade Science

Greg Kaleka
Greg Kaleka
39,019 Points

More 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
seal-mask
.a{fill-rule:evenodd;}techdegree
Göktuğ BATI
Python Web Development Techdegree Student 6,287 Points

it 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
kevin hudson
Courses Plus Student 11,584 Points

Suppose 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
Evgeni Vershinin
15,038 Points

I 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
Harris Handoko
3,932 Points

Just 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