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.
Ruiqi Mao16,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;
Steven Parker218,596 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.
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.