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 Finding the Troublemaker

Ruiqi Mao
Ruiqi Mao
16,675 Points
Posted by Ruiqi Mao
Ruiqi Mao
16,675 Points

My Solution without creating two common tables

Get the same result with the following code:

select subjects.id, students.id as student_id, first_name, last_name from schedule join classes on schedule.class_id = classes.id join subjects on classes.subject_id = subjects.id join students on schedule.student_id = students.id where (period_id = 5 and name = 'Science') or (period_id = 7 and name = 'Art') group by students.id having count(student_id) > 1;

Zaur Kandokhov
Zaur Kandokhov
12,276 Points

I have almost the same solution, which is more compact than in the video:

SELECT STUDENTS.* FROM STUDENTS
  INNER JOIN SCHEDULE ON STUDENTS.ID=SCHEDULE.STUDENT_ID
  INNER JOIN CLASSES ON CLASSES.ID=SCHEDULE.CLASS_ID
  INNER JOIN SUBJECTS ON SUBJECTS.ID=CLASSES.SUBJECT_ID
  WHERE (PERIOD_ID=5 AND NAME="Science") OR (PERIOD_ID=7 AND NAME="Art")
  GROUP BY STUDENTS.ID HAVING COUNT(1)>1;
kevin hudson
kevin hudson
Courses Plus Student 11,987 Points

I came close to this but knew in the back of my mind that this was calling for two CTE to be joined. So in the beginning when I was creating my Query I got stuck at the last conditional part. I had no Idea you could do this: 

WHERE (PERIOD_ID=5 AND NAME="Science") OR (PERIOD_ID=7 AND NAME="Art")

I was doing this and getting stuck :

WHERE SUBJECTS.NAME IN ( 'Science','Art') 
  AND CLASSES.PERIOD_ID  IN (5,7)

I got results but a long list and GROUP BY didn't help

2 Answers

Steven Parker
Steven Parker
231,269 Points
Steven Parker
Steven Parker
231,269 Points

CTE's (common table expressions) are usually just one of several ways a query can be constructed, but when they are referenced multiple times they can make the query much more compact. Since that's not the case in this particular example, they mainly only serve to illustrate the syntax, but they may also help to make the query's intention more clear at first glance.

Gavin Schilling
Gavin Schilling
37,904 Points
Gavin Schilling
Gavin Schilling
37,904 Points

Also by joining to a single fixed table like as follows you get rid of the needless redundancy but unless iterated over the remaining code like in this example may not be as DRY code as the above code but is instead highly readable:

WITH "5th Period Science Students" AS (
  SELECT student_id AS "Student ID" FROM SCHEDULE
    JOIN CLASSES ON classes.id = schedule.class_id
    JOIN SUBJECTS ON subjects.id = classes.subject_id
    WHERE classes.period_id = 5 AND subjects.name = "Science"
  ),

"7th Period Art Students" AS (
  SELECT student_id AS "Student ID" FROM SCHEDULE
    JOIN CLASSES ON classes.id = schedule.class_id
    JOIN SUBJECTS ON subjects.id = classes.subject_id
    WHERE classes.period_id = 7 AND subjects.name = "Art"
  )

SELECT (students.first_name || " " || students.last_name) AS "Troublemaker's Name" FROM STUDENTS
  JOIN "5th Period Science Students" ON students.id = "5th Period Science Students"."Student ID"
  JOIN "7th Period Art Students" ON students.id = "7th Period Art Students"."Student ID";

Moreover, the following example is perhaps the best of both worlds:

SELECT (students.first_name || " " || students.last_name) AS "Troublemaker's Name" FROM STUDENTS
  JOIN SCHEDULE ON students.id = schedule.student_id
  JOIN CLASSES ON classes.id = schedule.class_id
  JOIN SUBJECTS ON subjects.id = classes.subject_id
    WHERE (classes.period_id = 5 AND subjects.name = "Science")
       OR (classes.period_id = 7 AND subjects.name = "Art")
    GROUP BY students.id HAVING COUNT(schedule.student_id) > 1;

My question is without using another language like Python or PHP can the WHERE clause be made dynamic in a simple fashion that the WHERE OR statement could be done even drier using ONLY PURE SQL? Or using my first example, how might you create a PURELY SQL NO OTHER LANGUAGES function to DRY out the first example's two CT redundant portions of their respective statements? Is it as easy as creating a base CT statement and then somehow calling it inside the other two CT statements? If so, please elaborate how. Thanks as I have been combing over documentation regarding this for hours now.

Steven Parker
Steven Parker
231,269 Points
Steven Parker
Steven Parker
231,269 Points

Plain SQL doesn't have dynamic features, so database manufacturers often create an extension (usually proprietary) to make it programmable. Examples are Oracle's "PL/SQL" and SQL Server's "Transact-SQL".