Running Queries on Relational Data

Now that we’ve created our users and photos tables and connected them using a foreign key, it’s time to actually use that relationship.

Enough theory — let’s write some real SQL queries and see how this relationship helps us fetch meaningful data.

Adding More Photos to the Database

Right now, our database only has one photo. That’s not very exciting 😄

So let’s add a few more.

Remember:

• Each row in the photos table represents one photo
• Each photo belongs to one user
• The user_id column tells us which user owns the photo

Inserting Multiple Photos at Once

We can insert multiple rows in a single query by adding multiple sets of parentheses.

INSERT INTO photos (url, user_id)
VALUES
  ('http://2.jpeg', 1),
  ('http://25.jpeg', 1),
  ('http://36.jpeg', 1),
  ('http://44.jpeg', 2),
  ('http://52.jpeg', 3),
  ('http://63.jpeg', 4),
  ('http://77.jpeg', 4);

Each set of parentheses represents one photo.

Checking the Photos Table

Let’s verify what we inserted:

SELECT * FROM photos;

Now we can clearly see:

• User 1 has three photos
• User 2 has one photo
• User 3 has one photo
• User 4 has two photos

This is a perfect example of a one-to-many relationship:

One user → many photos

Query 1: Get All Photos by a Specific User