Databases SQL Reporting by Example Day 1: Joining Tables 7th Grade Science

Jordan young
PLUS
Jordan young
Courses Plus Student 5,024 Points

Hi. I did a totally different query, but generated the same result. Is there a preference for either method?

As Title but just out of curiosity, is there a preference?

'' SELECT DISTINCT TEACHERS.ID, FIRST_NAME, LAST_NAME FROM TEACHERS JOIN CLASSES ON CLASSES.TEACHER_ID = TEACHERS.ID WHERE SUBJECT_ID =21; ''

1 Answer

Gergely Bocz
MOD
Gergely Bocz
Treehouse Moderator 12,199 Points

Hi Jordan!

Your results are identical to the teachers because of a couple of reasons:

First of all, the Subject_id is separate for grades aswell, not only for subjects! This means, that if somebody from the 6th grade learns science, the corresponding subject_id (27) will be different from a 7th grader's subject_id(21) for example. You can check out the results yourself with this query:

SELECT DISTINCT * FROM SUBJECTS
JOIN CLASSES ON SUBJECTS.ID = CLASSES.SUBJECT_ID
WHERE SUBJECTS.NAME = "Science"

This means, that you specifying the subject_id is equal to specifying the subject name and the grade, just like the teacher did! However other databases aren't necessarily going to be structured like that, so you have to make a call based on the current database you are writing a query for!

I would also like to add, that while in such a small database you can easily search the subject_id you are looking for manually - which is what i assume you did in this case -, but in a larger database it may prove difficult, so i recommend joining the Classes and Subjects tables with a Where clause and specifying the subject's name, just like in the video.

I hope my explanation helps, if you have any more questions, feel free to ask!

All the best, Gergő