Thus far we have only been getting data from one table at a time. This is fine for simple takes, 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:

food Table:

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 ages. In the food table the Position column contains the family member who enjoys that dish.

It's only through shared column relationship such as this that allows tables to be joined together, so remember this when creating tables you wish to interact with eachother.

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 some 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 them created either create them using our MySQL Create Table lesson or some other method to create these tables

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 those rows where the Position exists in both the family and food tables

Display:

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

Compare the Tables:

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.