How do we create an address record that is associated or related with Jim Smith? Jim Smith's employee id is 1.

We can prove that by doing a quick query to confirm:

mysql> select * from names;
+----+----------+-----------+
| id | lastName | firstName |
+----+----------+-----------+
|  1 | Smith    | Jim       |
|  2 | Rocker   | Bob       |
+----+----------+-----------+
2 rows in set (0.00 sec)

So, we'll use that when inserting into the addresses table by putting 1 in the nameId field:

insert into addresses set nameId = 1, street = "1234 Main St", city = "Los Angeles", state = "CA", zip = "90000";

mysql> select * from addresses;
+----+--------+--------------+-------------+-------+-------+
| id | nameId | street       | city        | state | zip   |
+----+--------+--------------+-------------+-------+-------+
|  1 |      1 | 1234 Main St | Los Angeles | CA    | 90000 |
+----+--------+--------------+-------------+-------+-------+
1 row in set (0.00 sec)

nameId is essentially a pointer that points back to the names table. It’s what associates or relates the data to each other.

Let's take a quick and simple look at the select statement above. What is it? The select statement pulls data from the database. The most simple select statment is:

select * from [table];

The asterisk * basically asks the database to give us back all fields from the table. This is more of a quick and dirty way to see what's in the database. As we said above, you normally wouldn't use this in a real project. But, lets say you're exploring the data in a table, this is a great way to do that.

A more complicated example would be:

select [field], [field], [field] from [table];

The select statment can get even more complex and complicated. As an idea of what you can do with it, you can filter, sort, group, do math, calculate fields together, count, and join tables together. We'll get more into all of this in future lessons.

Okay, we have names in the names table and we have an address in the addresses table. How do we put the data together? We do a join on the table.

mysql> select * from names, addresses where names.id = addresses.nameId;
+----+----------+-----------+----+--------+--------------+-------------+-------+-------+
| id | lastName | firstName | id | nameId | street       | city        | state | zip   |
+----+----------+-----------+----+--------+--------------+-------------+-------+-------+
|  1 | Smith    | Jim       |  1 |      1 | 1234 Main St | Los Angeles | CA    | 90000 |
+----+----------+-----------+----+--------+--------------+-------------+-------+-------+
1 row in set (0.01 sec)

Now, wait a sec. You see multiple id columns. Because we used the * in the select query, we're going to get every single column back. One id is from the names table and one id is from the addresses table.

Good practices dictate that we always request the exact fields we need for our specific situation. For example, we might only need a last name and a zip code.

mysql> select names.lastName, addresses.zip from names, addresses where names.id = addresses.nameId;
+----------+-------+
| lastName | zip   |
+----------+-------+
| Smith    | 90000 |
+----------+-------+
1 row in set (0.00 sec)

The reason why we want to specify the columns is to save memory, increase query speed, and make our code more concise and readable. Also, if we haphazardly change our database schema, and change a column name, we'll catch the error in testing, whereas if we use *, the query will happily continue to work.

What are joins? Joins are the technical name for actually relating data in each table. We specify how we want to relate the table by using a join.

Let's take a look at how the join worked in the above example. First, let's take another look at the query:

select names.lastName, addresses.zip from names, addresses where names.id = addresses.nameId;

First, we're using select to tell MySQL that we'd like to get data from the database. We then specify the fields we'd like to get back using names.lastName and addresses.zip.

We then ask MySQL that we'd like to get data from two different tables, names and addresses.

We finally specify our join using names.id = addresses.nameId. This basically tells MySQL that we'd like names.id to match anything that is the same in addresses.nameId. If those two fields do match, it will combine the fields of the table and return them back to us.