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

Posted by linda wu
linda wu
3,016 Points

why distinct keyword does not work as expected

--WITH ELECTIVE_TEACHER_STUDENT AS ( SELECT DISTINCT TEACHERS.ID AS ID, SUBJECTS.NAME AS NAME, COUNT(STUDENTS.ID) AS STUDENTS FROM TEACHERS JOIN CLASSES ON TEACHERS.ID = CLASSES.TEACHER_ID JOIN SCHEDULE ON SCHEDULE.CLASS_ID = CLASSES.ID JOIN SUBJECTS ON SUBJECTS.ID = CLASSES.SUBJECT_ID JOIN STUDENTS ON STUDENTS.ID = SCHEDULE.STUDENT_ID WHERE SUBJECTS.GRADE IS NULL GROUP BY SUBJECTS.NAME --)

1 Answer

linda wu
linda wu
3,016 Points
linda wu
linda wu
3,016 Points

I found out if I remove and only keep distinct teachers.id then the query returns the unique teacher id. SELECT DISTINCT TEACHERS.ID AS ID FROM TEACHERS

But I don't understand how other column will affect the unique teacher id query result? Can someone give me some help please? Thanks a lot!

Ah, I got it.

I have alter the CTE a little bit, then it works fine.

--WITH ELECTIVE_TEACHER_STUDENT AS ( SELECT DISTINCT TEACHERS.ID AS ID, SUBJECTS.NAME AS NAME, SUBJECTS.GRADE, COUNT(STUDENTS.ID) AS STUDENTS FROM TEACHERS JOIN CLASSES ON TEACHERS.ID = CLASSES.TEACHER_ID JOIN SCHEDULE ON SCHEDULE.CLASS_ID = CLASSES.ID JOIN SUBJECTS ON SUBJECTS.ID = CLASSES.SUBJECT_ID JOIN STUDENTS ON STUDENTS.ID = SCHEDULE.STUDENT_ID WHERE SUBJECTS.GRADE IS NULL GROUP BY TEACHERS.ID --)

The distinct column needs to be matching with the group by column. But I still need someone give some clear explanation why it is working or not working with select distinct clause.