In the last part of this course, we set up the database to contain 4 different tables to demonstrate how to handle one-to-many relationships and many-to-many relationships.
In this final chapter of the course, we are going to go into the SQL for reading information from tables in these associations. More simply: how do you
SELECT from multiple tables?
In our schema for the condominium domain, we had a one to many relationship between the apartment table and the dog table.
This is what one line of the
dog table looks like:
And this is what a line of the apartment table looks like.
To get the
name and the
dog_id 1, we need to to create a SQL query that asks 2 tables for their content.
Here's how we could do that:
SELECT apartment.apt_number, dog.name, dog.breed FROM dog INNER JOIN apartment ON dog.apt_id = apartment.apt_id WHERE dog.dog_id = 2;
We have entered the realm of the JOINS!
Joins allow you to ask multiple tables for information at the same time.
As you can see, in the
SELECT statement, I am choosing all the columns that I want to display. The
FROM statement contains the main table that I'm interested in. The
JOIN statement tells the database which other tables I want to query. The
ON statement reveals which columns in the two tables are linked.
In one-to-many relationships, like between the apartment and dog tables, the SQL
ON statement tells the database that the Foreign Key in the dog table (
apt_id ) is the apartment table's Primary Key.
Notice in the
SELECT statement, we are prefixing the column names with the table names and connecting them with a dot (.) like this
apartment.apt_number . This way, if we have columns with the same name but different tables, the database won't get confused!
There are different types of
JOIN s. In the SQL above, I used an
INNER JOIN .
INNER JOIN selects just the rows where there is a match in the
ON clause. The
ON clause in the SQL above is linking back from the Foreign Key of the Dog table to the Primary Key of the apartment table.
INNER JOIN is the most often kind of join and it is often shortened to just be
JOIN like this:
SELECT apartment.apt_number, dog.name, dog.breed FROM dog JOIN apartment ON dog.apt_id = apartment.apt_id WHERE dog.dog_id = 2;
You'd use the other types of joins when you want to list more than the rows that match in the
So who is
dog_id 2 and where does he or she live?
This is what phpMyAdmin returns from the query above:
Now let's find out all Many associated with the One! Which dogs live in the apartment with
apt_id 2? Let's try to get a list of all the dogs that live in one apartment.
SELECT apartment.apt_number, dog.name, dog.breed FROM apartment JOIN dog ON apartment.apt_id = dog.apt_id WHERE apartment.apt_id = 2;
SELECT statement, we choose all the columns we want to display. This time the
FROM statement contains
apartment because that is the primary table we are going to be searching.
This is what gets returned from my database:
Now if this list contained 100 rows, and I just wanted to look at the first 10, I could tack on a
SELECT apartment.apt_number, dog.name, dog.breed FROM apartment INNER JOIN dog ON apartment.apt_id = dog.apt_id WHERE apartment.apt_id = 2 LIMIT 50;
(But I hope there are not 50 dogs living in
apt_id 2! 🐶)
If I wanted to order the list by the breed, the SQL would look like this:
SELECT apartment.apt_number, dog.name, dog.breed FROM apartment INNER JOIN dog ON apartment.apt_id = dog.apt_id WHERE apartment.apt_id = 2 ORDER BY breed;
Selecting multiple rows in a many-to-many relationship
Remember the cat - apartment relationship from our domain diagram? A cat could be fed by multiple apartments and an apartment could feed multiple cats. The association between the cat and the apartment tables includes the
fed_at table. The
fed_at table is the junction table.
Let's list all the cats' names, and the apartments that they live in and what they eat in each apartment. This involves connecting 3 tables!
And... let's order the list by the
SELECT apartment.apt_number, cat.name, fed_at.fed FROM fed_at JOIN cat ON cat.cat_id = fed_at.cat_id JOIN apartment ON apartment.apt_id = fed_at.apt_id ORDER BY apartment.apt_number;
Because we are searching the junction table we need to have 2
JOIN statements - 1 for each of the associations that the
fed_at is connecting.
Now, let's select the same information but just for an individual cat - the cat whose
cat_id is 1.
We'll just add at a
SELECT apartment.apt_number, cat.name, fed_at.fed FROM fed_at JOIN cat ON cat.cat_id = fed_at.cat_id JOIN apartment ON apartment.apt_id = fed_at.apt_id WHERE fed_at.cat_id = 1;
And this is what phpMyAdmin returns:
That is what Haldeman ate and where he ate it!
The first part of this course was about how to design a database with UML diagrams. I went over the basic types of relationships between classes and how to describe the relationships and domains with UML .
Then in the second part of the course, we implemented a database schema using the GUI phpMyAdmin in a MySQL database.
In this part of the class, I reviewed CRUD operations (Create, Read, Update, and Destroy). We learned CRUD operations using the example of the condominium database.
In this last chapter, we learned about more complex queries - queries that get information from multiple tables. The query with the single join was used for the tables connected by a one-to-many relationship. The query with two joins was used for the tables connected by a Many to Many relationship.
We've gone from database theory to database design to database practice!
I hope this class will help you
SELECT * FROM database_success;