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 3: Getting Good at Grouping Multi-Subject Teachers

Posted by Joshua Kaufman
Joshua Kaufman
19,193 Points

Prompt 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
STAFF
Ben Deitch
Treehouse Teacher
Ben Deitch
Ben Deitch
Treehouse Teacher

Hey 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 :)

Matthew Stevenson
Matthew Stevenson
4,877 Points
Matthew Stevenson
Matthew Stevenson
4,877 Points

Hi 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!

-- Do any teachers teach multiple subjects? If so, which teachers?
WITH multi_t AS (SELECT * FROM CLASSES
    GROUP BY TEACHER_ID
    HAVING COUNT(DISTINCT SUBJECT_ID) > 1)
SELECT t.*, s.NAME AS "Subject" FROM CLASSES AS c
INNER JOIN multi_t ON c.TEACHER_ID = multi_t.TEACHER_ID
INNER JOIN TEACHERS AS t ON t.ID = c.TEACHER_ID
INNER JOIN SUBJECTS AS s ON s.ID = c.SUBJECT_ID
GROUP BY c.SUBJECT_ID
;
Boban Talevski
Boban Talevski
24,793 Points
Boban Talevski
Boban Talevski
24,793 Points

This 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
Giuseppe Ardito
14,130 Points
Giuseppe Ardito
Giuseppe Ardito
14,130 Points

Great! 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
Mark Chesney
11,747 Points
Mark Chesney
Mark Chesney
11,747 Points

Excellent. 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
Mia Filisch
16,117 Points
Mia Filisch
Mia Filisch
16,117 Points

This 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
Jamesha Chapple
2,813 Points
Jamesha Chapple
Jamesha Chapple
2,813 Points

Why does "TEACHERS." work in SELECT TEACHERS. * FROM TEACHERS ? I haven't seen any other use of the "." like that

Faisal Khan
Faisal Khan
2,712 Points

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