Now the last type of relationship is the many to many relationship.
And this is probably one of the most complex and least used types of relationships.
What is a many to many relationship?
Let's think about it on the bigger scale first.
Let's say you have a student in your school.
Well, they could be in many classes, right?
One student can be subscribed or enrolled in many classes.
So maybe I am a student.
I take English, maths and physics at my school.
One student.
Many classes.
This direction of the relationship.
One student such as Angela can be subscribed to many classes such as English, maths, physics.
Now on the other hand, we have the opposite relationship.
We could have one class, let's say English class that is taken by many students in this English class.
Maybe there is Angela, but also Amy and Emily and lots of different students in this class.
So in this case we have a one so one class that's linked to many students.
So if you have to one to many relationships, one from each side of the table, then what you get overall
is a many to many relationship.
And this type of relationship does happen occasionally in our data storage needs.
And to represent this relationship, usually in Postgres, what we do is we create a new table that
represents just that relationship.
And we can't represent the relationship alone.
Using a single relationship.
Each record in the enrollment table will have a student ID.
So student number one.
And they also have a class ID.
So class one.
And then for each class that the student subscribes to we would have a separate record.
So student one is also subscribed to class three.
And maybe student two is subscribed to class one.
And so on and so forth.
And that table in its entirety and all the records can represent this many to many relationship.
In this case, if we have two tables and we straight up wanted to represent the many to many relationship,
we would have two crow's feet like this, linking some sort of field with each other in the table.
But because in Postgres we have this need for the extra table to create this relationship, I often
represent the database design like this, so that you can see that the student ID is a many to one relationship
with the enrollment table.
Student ID and the class ID has a many to one relationship with this field called class ID in the enrollment
table.
Let's see this again in action using the code here, which you will of course find in your queries.
So let's create these two new tables in Pgadmin.
The class table that has just the title of the class, the name of the class, and a primary key that
uniquely identifies that class.
And then the enrollment table which has the student ID, class ID and both of these are set up as foreign
keys because they have that references keyword.
This one references the student table ID field.
This one references the class table ID field, and then we set the primary key as a combination of these
two IDs.
So it could be zero one or 1 or 2.
And the reason why it's primary key is because each of these enrollment records are unique.
So if we had student one enrolled in class two, we should not have two duplicate records where we have
another one which represents student one enrolled in class two.
That's what that primary key part does, and ensures that each of the records in the enrollment table
are unique.
Now let's create this relationship.
So now we can go ahead and refresh.
And we should see our new tables class and enrollment.
Let's add some data into there so that we can work with it.
Go ahead and copy all of the lines under this data section in queries dot SQL.
And.
Paste it into where we run our queries.
Let's do that and add all of these values.
What I've done is I've created yet another student.
So we have this student which will have an ID of two.
And remember previously we had the other student that had an ID of one, which was just me.
It was this student right here.
Now we're creating some new values into the class table.
So we're adding three classes that the school offers English literature, maths and physics.
Because they added an order this one will have an ID of one.
This one two.
This one three.
Now the final part we create our enrollment records.
We have some values here.
And I've separated these two even though you could add all four in the same go.
But this is just easier to look at.
The first value corresponds to the student ID, so this is the first student.
And and they are being enrolled into class one and class two.
So that's English and maths.
Now the other insert has the second student the student with ID two in this case Jack Bauer who is enrolled
into class two maths and class three physics.
And I think that's going to set him up for saving the world quite nicely.
Now let's see how we can use our join keyword to take advantage of this many to many relationship that
we have set up.
In this case, I'm selecting everything from the enrollment table, and I'm going to join it to two
tables.
So I have two join statements in here.
The first one is the student table.
And the criteria is that the student ID must equal the enrollment tables student ID in the enrollment
table.
Remember we had a foreign key which is called student ID, and we want that to match the primary key
on the student table so that we're getting the same student each time.
Next we're going to join it to the class table, making sure that the class ID matches the enrollment
table's class ID.
Now, when we run this entire SQL command, we end up with this big joined table.
And we have three tables right here.
This is the enrollment table.
This is the student table.
And this is the class table.
And every unique record in the enrollment table.
So every single relationship is represented in this.
And you'll see the other parts that are duplicated such as the class or the student.
You'll see them show up many times.
But the unique part is making sure that every single record in that enrollment table is shown and displayed
here.
So now, if we wanted to know, for example, well, how many students are taking maths, we could sum
that up.
We would end up with two.
There's only two students in math class.
Or if we wanted to know well which classes is Angela taking?
Well, that's maths and English.
Or if we wanted to know which classes is Angela and Jack taking together?
Well, we would be able to query this table and find the overlap as well.
And this is how we can use a many to many relationship to represent data that can have a many to one
relationship on both sides.
So one student goes to many classes, but one class also can have many students.
Now I want to introduce you to a command that you might see in the future, which is called an alias.
And we can establish an alias or an alternative name using the as keyword.
In this case I'm selecting certain fields.
So there is a field called firstName.
There's a field called LastName.
There's a field called title.
And in the student table there is a field called ID.
But I don't want to call it ID.
Instead I want to call it stud or student for short.
That's just how I want to format the table that comes out.
I can use this as keyword, and what that's going to do is when it generates this final table and selects
all the fields that I want, it's going to rename this field into stud.
And that can be helpful when you want to search through something that has many.
Any different fields called ID, you can give them a different name each time, or maybe you just want
to name it something else that you choose.
It's easier for you to understand what's going on, but this is how you can use as to create an alias.
Another way we can use the alias is to simplify our SQL queries.
Notice here that there is a lot of text, and each time when we want to tap into a particular table
to define a field we're interested in, such as student ID, class ID, enrollment, class ID, etcetera,
we have to type out each of the table names individually, and that can get really tiring if your queries
become longer and longer.
One way to shorten this is to use an alias.
Not only are we saying that the student ID is now called stud, but we're also setting aliases for our
tables.
We're going to call enrollment E, student S and class C.
So now whenever we want to use the names of those tables such as student ID enrollment or student ID
class ID, we can now use their alias instead.
And that makes our code a lot shorter.
And many times it just means you don't have to type out the entire name of your table.
So you'll see this occurring frequently out in the wild.
Now, one thing that a lot of people don't know is that the As keyword is actually completely optional.
You can achieve exactly the same type of alias behavior.
You can set the alias without that as keyword.
You can say enrollment space E, and that space is already enough to tell SQL that you're setting up
an alias.
You're saying, let's call enrollment E, let's call student S, and let's call class C.
This is easy enough to understand when it comes to the name of tables, but I recommend this is just
a recommendation on when you want to rename fields such as student ID here as a different name that
you keep using that as keyword.
It just keeps it a little bit easier to understand.
For some reason.
I find in the select statement if I'm selecting a field and giving it an alias, if I don't have that
as keyword, I get confused more frequently.
So that's just a tip.
You don't have to listen, but it has helped me.