Everything PHP: Multiple Table Joins
Posted on December 30th, 2009
Continuing our series on MySQL table joins, today we’ll be taking a look at how to join multiple tables together. You may need to join multiple tables when you have data spread across multiple tables with reference points relating them.
The syntax for joining multiple tables versus two tables is similar. You may even recognize some of the operators from previous articles. First, let’s have a look at a two table join:
[sql]SELECT * FROM table1 JOIN table2 ON table1.id = table2.id[/sql]
With a multi-table join, we will need a field in the “joining tables” that will relate to a field in the initial table. These matching fields do not need to be the same for each “joining table”. Now, let’s take a look at our feature presentation:
[sql]SELECT * FROM table1 JOIN (table2, table3, table4) ON table1.id = table2.id AND table1.name = table3.name AND table1.email = table4.email[/sql]
There is no hard limit on the number of tables you can join; however, there is a practical limit. If you’re joining more than ten tables together, you may want to look at alternate methods, such as multiple queries. If you’re joining more than ten tables constantly in one application, your data may be spread across too many tables, and you may want to look at combining some of them.
Next week we’ll be going over the different types of joins (left, right, outer, inner, natural).
Tip: If you have phpMyAdmin installed on your server, you can easily run SQL queries by selecting a database and going to the “SQL” tab at the top of the page.
Posted in Development | | | Digg This | del.icio.us | Technorati
Related Topics:
Everything PHP: Selecting Table Specific Fields on JoinsEverything PHP: Table JOINWeb Development: TablesEverything PHP: SQL: UPDATEEverything PHP: SQL: INSERT
1 Comment
Sorry, the comment form is closed at this time.
While the JOIN syntax provided is valid, it is highly unusual. I’ve been a DBA for a long time, and I’ve never actually encountered a SQL statement which uses the /table_references/ variant of the ANSI join clause.
One will usually see:
1.
SELECT *
FROM table1
JOIN table2
ON table1.id = table2.id
JOIN table3
ON table1.name = table3.name
JOIN table4
ON table1.email = table4.email
or
SELECT *
FROM table1,table2,table3,table4
WHERE table1.id = table2.id
AND table1.name = table3.name
AND table1.email = table4.email