Multiple join table in mysql

Question

mysql> SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count
    -> FROM tutorials_tbl a, tcount_tbl b
    -> WHERE a.tutorial_author = b.tutorial_author;
+-------------+-----------------+----------------+
| tutorial_id | tutorial_author | tutorial_count |
+-------------+-----------------+----------------+
|           1 | John Poul       |              1 |
|           3 | Sanjay          |              1 |
+-------------+-----------------+----------------+
2 rows in set (0.01 sec)
mysql>

This is the mysql join query tutorial on http://www.tutorialspoint.com/mysql/mysql-using-joins.htm. But I need to join many tables. In that case.. How WHERE statement would look like? Please help...

Answer 1

That's a very old-fashioned way of writing joins.

New and funky way is to write it like this:

SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count 
FROM tutorials_tbl a 
INNER JOIN tcount_tbl b ON a.tutorial_author = b.tutorial_author

To add more tables you just add more JOIN clauses. And since your tutorial seems a little outdated, here's the best explanation to joins I've ever seen, simple and beautiful and short: A Visual Explanation of SQL Joins

Answer 2

In FROM clause, you determine which tables to join via JOIN keyword, and describe common columns to pair using ON keyword.

SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count 
FROM tutorials_tbl a 
  JOIN tcount_tbl b ON a.tutorial_author = b.tutorial_author

Or you may use USING since shared column names are equal:

SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count 
FROM tutorials_tbl a 
  JOIN tcount_tbl b USING(tutorial_author)