Professional LAMP: Linux®, Apache, MySQL®, and PHP5 Web Development

4.2. Querying Multiple Tables

You've got your vehicle inventory set up, and you can use the basics to perform simple queries against one table at a time. But you'd like to see a little more—maybe even a query that links the make and model of each car to its individual record. For this, you'd use joins.

In SQL, joins are a way of linking two or more tables on a specified column or columns. In MySQL, there are two types of joins that are most commonly used: inner joins and outer joins.

4.2.1. Inner Joins

Inner joins are a way of taking two tables and combining their rows based on a commonly shared value for each row. When you perform an inner join in a SELECT query, you simply specify the tables to be joined in the FROM clause, and provide a hint on what columns they have in common. A generic abstraction of the MySQL INNER JOIN syntax looks like the following:

SELECT [column names] FROM [table1] INNER JOIN [table2] ON [table1.column] =
[table2.column]

Using the hypothetical car lot inventory, you want the model of each vehicle to return in the results alongside each vehicle's specific information. To accomplish this, your query would look something like this:

SELECT Models.model_name, New_Vehicles.color, New_Vehicles.modelyear FROM Models
INNER JOIN New_Vehicles ON Models.model_id = New_Vehicles.model_id;

Running this query against your database yields the following results:

+------------+--------------------------------+-----------+ | model_name | color | modelyear | +------------+--------------------------------+-----------+ ...