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 7th Grade Science

Greg Kaleka
Greg Kaleka
39,021 Points
Posted by Greg Kaleka
Greg Kaleka
39,021 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
Göktuğ BATI
.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,987 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
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
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