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 trialRuiqi Mao
16,675 PointsMy 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;
kevin hudson
Courses Plus Student 11,987 PointsI 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
231,236 PointsCTE'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
37,904 PointsAlso 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
231,236 PointsPlain 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".
Zaur Kandokhov
12,276 PointsZaur Kandokhov
12,276 PointsI have almost the same solution, which is more compact than in the video: