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

Onur Dursun
Onur Dursun
6,532 Points
Posted by Onur Dursun
Onur Dursun
6,532 Points

Multiple subjects with also the name of them?

Hello,

This question is inteded to be asked in the forum topic of Databases > SQL Reporting by Example > Day 3: Getting Good at Grouping. But somehow, the interface only let me choose the topic of "Databases" and i dont see it going into the right topic. I may need a little help from the forum moderators.

The question is as such:

SELECT TEACHERS.*, SUBJECTS.NAME FROM TEACHERS
  JOIN CLASSES ON CLASSES.TEACHER_ID = TEACHERS.ID
  JOIN SUBJECTS ON CLASSES.SUBJECT_ID = SUBJECTS.ID
  GROUP BY TEACHER_ID HAVING COUNT(DISTINCT SUBJECT_ID) > 1;

Above querry results in showing the teacher (Janis Ambrose) who happens to be teaching more than 1 subject, alongside only 1 of the names of those subjects (since it is grouped by teacher_id).

If i would like to also querry for what subjects are those, how should i modify my querry accordingly?

2 Answers

Steven Parker
Steven Parker
231,248 Points
Steven Parker
Steven Parker
231,248 Points

To get the full set of topic "breadcrumbs", you must start the question by clicking on the "Get Help" button under the "Questions?" tab on the video page. If that's what you did, you may want to report the lack of breadcrumbs to Support as a bug. And even moderators cannot add breadcrumbs to an existing question.

And your question made me realize the video starts out by asking "which teachers, and which subjects do they teach?" but then never shows how to get the second half of that data. Perhaps this might also be worth a bug report.

But to do this you need a list aggregate function, and SQLite has one called GROUP_CONCAT but I don't think it's introduced in this course. Here's a suggested replacement for your top line using it:

SELECT TEACHERS.*, GROUP_CONCAT(DISTINCT SUBJECTS.NAME) AS Subjects FROM TEACHERS

I also added "DISTINCT" to insure that each topic is only listed once.

Onur Dursun
Onur Dursun
6,532 Points
Onur Dursun
Onur Dursun
6,532 Points

Thank you Steven,

You are right, GROUP_CONCAT was not introduced in this course but it is exactly what i was looking for.

NOTE: (The thing about the start of the video) I saw that there is a discussion about it on the forum where the instructor is warned about it. He wrote that he will do another video to cover this issue.