• 20 hours
  • Medium

Free online content available in this course.

course.header.alt.is_video

course.header.alt.is_certifying

Got it!

Last updated on 11/25/19

Perform SELECTS with multiple tables

Log in or subscribe for free to enjoy all this course has to offer!

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:

dog_id

apt_id

name

breed

bites

1

2

Mr. Pickles

Yorkshire Terrier

1

And this is what a line of the apartment table looks like.

apt_id

apt_number

size

bedrooms

2

1B

1200

3

To get the  name  and the  apt_number  for  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  ON  clause.

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;

In the  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  LIMIT  statement:

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  apt_number . 

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  WHERE  clause:

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:

The what and the where of the meals of the cat named Haldeman
The what and the where of the meals of the cat named Haldeman

That is what Haldeman ate and where he ate it!

Course recap

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;

 

 

 

 

 

Example of certificate of achievement
Example of certificate of achievement