MySQL Joins

Thus far we have only been getting data from one table at a time. This is fine for simple tasks, but in most real world MySQL usage you will often need to get data from multiple tables in a single query.
The act of joining in MySQL refers to smashing two or more tables into a single table. This means everything you have learned so far can be applied after you've created this new, joined table.

MySQL Join Table Setup

We like to show examples and code before we explain anything in detail, so here is how you would combine two tables into one using MySQL. The two tables we will be using relate to a families eating habits.

family Table:

Position	Age
Dad	41
Mom	45
Daughter	17
Dog

food Table:

Meal	Position
Steak	Dad
Salad	Mom
Spinach Soup
Tacos	Dad

The important thing to note here is that the column Position contains information that can tie these two tables together. In the "family" table, the Position column contains all the members of the family and their respective ages. In the "food" table the Position column contains the family member who enjoys that dish.
It's only through a shared column relationship such as this that tables can be joined together, so remember this when creating tables you wish to have interact with each other.

MySQL Join Simple Example

Let's imagine that we wanted to SELECT all the dishes that were liked by a family member. If you remember from the previous lesson, this is a situation when we need to use the WHERE clause. We want to SELECT all the dishes WHERE a family member likes it.
We will be performing a generic join of these two tables using the Position column from each table as the connector.
Note: This example assumes you have created the MySQL tables "food" and "family". If you do not have either of them created, you can either create them using our MySQL Create Table lesson or do it manually yourself.

PHP and MySQL Code:

<?php
// Make a MySQL Connection
// Construct our join query
$query = "SELECT family.Position, food.Meal ".
 "FROM family, food ".
 "WHERE family.Position = food.Position";
  
$result = mysql_query($query) or die(mysql_error());


// Print out the contents of each row into a table 
while($row = mysql_fetch_array($result)){
 echo $row['Position']. " - ". $row['Meal'];
 echo "<br />";
}
?>

The statement "WHERE family.Position = food.Position" will restrict the results to the rows where the Position exists in both the "family" and "food" tables.

Display:

Dad - Steak
Mom - Salad
Dad - Tacos

Those are the results of our PHP script. Let's analyze the tables to make sure we agree with these results.

Compare the Tables:

Position	Age
Dad	41
Mom	45
Daughter	17
Dog

Meal	Position
Steak	Dad
Salad	Mom
Spinach Soup
Tacos	Dad

Our results show that there were three meals that were liked by family members. And by manually perusing the tables it looks like there were indeed three meals liked by family members.
Note: This is a very simple example of a join. If you do not understand it yet do not despair. Joins are a very hard concept to grasp for beginning MySQL developers.

Posted in: MySQL