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 trialJoshua Kaufman
19,193 PointsPrompt asks for a different thing than the video shows
SQL Playground Prompt asks "-- Do any teachers teach multiple subjects? If so, who and which subjects?" The video covers only listing the multi-subject teachers, but not the list of subjects they teach. This might require a second query, but I wanted to point it out for Ben in case he wants to change the prompt in SQL Playground to better match the video.
Great course so far!
4 Answers
Ben Deitch
Treehouse TeacherHey Joshua!
Good catch! Looks like I'll need to record a new solution including the 'which subjects' part, but until then I've changed the prompt to not include it. Thanks for letting me know :)
Boban Talevski
24,793 PointsThis is my solution for the report including the teachers along with the subjects they teach. Not sure if it can be more optimized
SELECT FIRST_NAME, LAST_NAME, NAME
FROM TEACHERS
JOIN CLASSES ON CLASSES.TEACHER_ID = TEACHERS.ID
JOIN SUBJECTS ON SUBJECTS.ID = CLASSES.SUBJECT_ID
GROUP BY NAME
HAVING TEACHERS.ID IN (
SELECT TEACHER_ID FROM (
SELECT TEACHER_ID, COUNT(DISTINCT SUBJECT_ID) num_subjects FROM CLASSES
JOIN TEACHERS ON TEACHERS.ID = CLASSES.TEACHER_ID
GROUP BY TEACHER_ID
HAVING num_subjects > 1
)
)
Giuseppe Ardito
14,130 PointsGreat! It looks good!
I simplified it a little:
SELECT T.ID, FIRST_NAME, LAST_NAME, NAME FROM CLASSES C
JOIN SUBJECTS ON SUBJECTS.ID = C.SUBJECT_ID
JOIN TEACHERS T ON T.ID = C.TEACHER_ID
GROUP BY NAME HAVING T.ID IN (SELECT TEACHER_ID FROM CLASSES
GROUP BY TEACHER_ID
HAVING COUNT(DISTINCT SUBJECT_ID) > 1);
Mark Chesney
11,747 PointsExcellent. I have similar results. I find mine is simple because there's only one use of GROUP BY
and HAVING
:
-- Do any teachers teach multiple subjects? If so, which teachers?
SELECT DISTINCT FIRST_NAME, LAST_NAME, NAME FROM TEACHERS
JOIN CLASSES ON TEACHERS.ID = CLASSES.TEACHER_ID
JOIN SUBJECTS ON SUBJECTS.ID = CLASSES.SUBJECT_ID
WHERE TEACHERS.ID IN (
SELECT TEACHER_ID FROM CLASSES
GROUP BY TEACHER_ID HAVING COUNT(DISTINCT SUBJECT_ID) > 1
)
Mia Filisch
16,117 PointsThis is my favourite solution! I keep forgetting to just use a cheeky DISTINCT
when I don't really need the extra benefits of GROUP BY
. My first approach (before watching the solution in the video) was outrageous as I forgot about DISTINCT
entirely and used 2 CTEs instead to aggregate in two stages 😂. All good learning I suppose....
Jamesha Chapple
2,813 PointsWhy does "TEACHERS." work in SELECT TEACHERS. * FROM TEACHERS ? I haven't seen any other use of the "." like that
Faisal Khan
2,712 PointsI don't think it has been explained before.
In general when you want to choose a specific column from a table you use: table.column
(For example, suppose you want to choose column "first_name" from "teachers" table you would do: teachers.first_name)
So: TEACHERS. * means that you want to select all the columns from TEACHERS. Remember that * means to select all.
Matthew Stevenson
4,877 PointsMatthew Stevenson
4,877 PointsHi Ben,
Really enjoying these tasks to practice all I have learned on the beginner SQL track. I've just completed this lesson and wanted to let you know that the prompt in the playground still includes "If so, which teachers".
and here's my solution!