Joining three or more database tables seems a bit tricky. But once you know how to join three tables you can literally join any number of database tables. For the purpose of this tutorial I have four tables named Table1 to Table4. Field names are given as F[Table Number][Alphabet], eg: F1A. Table names with double numbers are foreign keys. The names of foreign Keys are given as follows: F[Table Number][Table Number which it points to][Alphabet]. Foreign Key of Table1 points to Table2 and that of Table2 points to Table3 and finally that of Table3 points to Table4.
For simplicity I select all fields (SELECT *) and select all records (WHERE 1) in the SQL
Simple Two Table Joint
To start with I will just demonstrate a simple join statement.
Select * FROM Table1 as T1 JOIN Table2 as T2 ON T1.F12A=T2.F2A WHERE 1
Three Table Join Statement
To make a 3 table Sql Join we must first do a two table join and then join this result with the remaining table.
SELECT * FROM ( Select * FROM Table1 as T1 JOIN Table2 as T2 ON T1.F12A=T2.F2A WHERE 1 ) as T12 JOIN Table3 as T3 ON T12.F23A=T3.F3A WHERE 1
Table T12 comes from the joining of Table1 and Table2.
Join Four Tables With SQL
With four tables we can either follow the same strategy that we had done with 3 tables or we can use a slightly different technique. If we use the above linear Joining this is what it would look like.
SELECT * FROM( SELECT * FROM ( SELECT * FROM Table1 as T1 JOIN Table2 as T2 ON T1.F12A=T2.F2A WHERE 1 ) as T12 JOIN Table3 as T3 ON T12.F2A=T3.F3A WHERE 1 ) as T123 JOIN T4 ON T123.F34A=T4.F4A WHERE 1
The second method is a binary tree approach. It groups that table into sub-joins and combine the results rather than a linear approach.
SELECT * FROM ( SELECT * FROM ( Select * FROM Table1 as T1 JOIN Table2 as T2 ON T1.F12A=T2.F2A WHERE 1 ) as T12 JOIN ( SELECT * FROM ( Select * FROM Table3 as T3 JOIN Table4 as T4 ON T1.F34A=T4.F4A WHERE 1 ) as T34 ON T12.F23A=T34.F3A WHERE 1
Combine Any Number of Tables
The above techniques can be combinations to join any number of tables. But one thing you have to keep in mind is that join statements are expensive. That is, they consume a lot of memory and processing time. So it advice to make the joint statements as simple as possible. Special care have to be given for the database design. A bad database design will consume server resources in an exponential rate as the database gets bigger, especially with JOIN statements.
Another thing to keep in mid is the order of JOIN when multiple joins are maid. Whenever possible the first join must be the join that retrieves the minimum records. This will save unwanted joints that will waist server resources.
If you have any questions or suggestions feel free to leave then in the comments section.