Hello everyone Eddie the Magic Monk here. Welcome to our final SQL tutorial for
today I'm probably not gonna do another one for a while so this is probably
enough for you guys to understand most of SQL after watching this tutorial so
as you can see I have three tables in this database okay I've got a list of
subjects in a school a list of students in a school and a list of which students
are enrolled in which subject so what are we going to teach you today is how
to join these three tables together into one big table so if I create another
query so let's open up select query 9 let's edit this query and what we're
gonna do is we're gonna select star from enrollment and that is obviously gonna
create just a copy of the enrollment enrollment table which has subject ID
and student ID so let's just do that let's do select subject ID student ID
now just in preparation for the join what I'm gonna do is I'm gonna give the
enrollment table an alias or in other words a nicknames Enrollment E so the
nickname for enrollment is now E so whenever I want to refer to a field in
that table I can just put E.subject ID E.student ID that means that
these two fields belong to the enrollment table right now it's sort of
pointless to do that but you can see why we do it soon now if we want to
join two tables together for example I'm gonna now join the Enrollment table and the
subject table so Enrollment E I'm going to put in here subjects
S S represents a variable for the subjects table so I'm gonna select E.
subject ID and S. subject subject name now here's where it's important you
have to say how you want them to join you have to put where e.subject ID
is equal to s.subject ID because if you don't put this there it's not going
to understand how you want to join them so now let's run this so design run and
you can see next to each subject I got subject ID one English subject ID
one English and subject two maths and so on so let's now join up the join up the
students with the Enrollment table as well so let's go back to SQL let's put
students students I can't put s because then I have two tables with the same
nickname so I'm going to put students as
U okay so because I have three tables I now have to say how I want them to
join so where Enrollment.student ID
is equal to U. so inside the students table I got s_ID s_ID
so that's how those two tables will be linked so I got the enrollment table
which has student ID and this is the same as in the students table we got the
students ID so for an example next to student ID number one I'm gonna have the
name Smith so I'm gonna put in here comma U.last name so if I run this
it's gonna give me it's gonna give me a syntax error which is unfortunate where
did I go wrong and forgot the AND key word so run it so
it works now I have the subject name and student name that's enrolled in that
subject so three tables linked into one so obviously you can add more conditions
to that so if I only want to list the people in the math subject then I can do
and S.Subject name is equal to English or maths so if I run this then I
should only get the maths subject only got the maths subject and the people
enrolled in the maths subject okay I hope this tutorial helps you guys join tables
together thanks for watching see you next time.