MySQl Joins

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.
You can use multiple tables in your single SQL query. The act of joining in MySQL refers to smashing two or more tables into a single table.
You can use JOINS in SELECT, UPDATE and DELETE statements to join MySQL tables. We will see an example of LEFT JOIN also which is different from simple MySQL JOIN.

Using Joins at Command Prompt:

Suppose we have two tables javatcount_tbl and javatutorials_tbl, in JAVATUTORIALS. A complete listing is given below:

Example:

Try out the following examples:

root@host# mysql -u root -p password;
Enter password:*******
mysql> use JAVATUTORIALS;
Database changed
mysql> SELECT * FROM javatcount_tbl;
+----------------------+---------------------+
| javatutorial_author | javatutorial_count |
+----------------------+---------------------+
| mahran               |                  20 |
| mahnaz               |                NULL |
| Jen                  |                NULL |
| Gill                 |                  20 |
| Karhik               |                   1 |
| Darshan              |                   1 |
+----------------------+---------------------+
6 rows in set (0.01 sec)
mysql> SELECT * from javatutorials_tbl;
+-------------+----------------+-----------------+-----------------+
| tutorial_id | tutorial_title | tutorial_author | submission_date |
+-------------+----------------+-----------------+-----------------+
|           1 | Learn PHP      | Gemini          | 2007-05-24      |
|           2 | Learn MySQL    | Karthik         | 2007-05-24      |
|           3 | JAVA Tutorial  | Darshan         | 2007-05-06      |
+-------------+----------------+-----------------+-----------------+
3 rows in set (0.00 sec)
mysql>

Now we can write a SQL query to join these two tables. This query will select all the authors from table javatutorials_tbl and will pickup corresponding number of tutorials from java_tcount_tbl.

mysql> SELECT a.javatutorial_id, a.javatutorial_author, b.javatutorial_count
    -> FROM javatutorials_tbl a, javatcount_tbl b
    -> WHERE a.javatutorial_author = b.javatutorial_author;
+----------------+---------------------+--------------------+
|javatutorial_id | javatutorial_author | javatutorial_count |
+----------------+---------------------+--------------------+
|              1 | Karthik             |                  1 |
|              3 | Darshan             |                  1 |
+----------------+---------------------+--------------------+
2 rows in set (0.01 sec)
mysql>

Using Joins in PHP Script:

You can use any of the above-mentioned SQL query in PHP script. You only need to pass SQL query into PHP function mysql_query() and then you will fetch results in usual way.

Example:

Try out the following example:

<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
  die('Could not connect: ' . mysql_error());
}
$sql = 'SELECT a.javatutorial_id, a.javatutorial_author, b.javatutorial_count
     FROM javatutorials_tbl a, javatcount_tbl b
    WHERE a.javatutorial_author = b.javatutorial_author;';

mysql_select_db('JAVATUTORIALS');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
  die('Could not get data: ' . mysql_error());
}
while($row = mysql_fetch_array($retval, MYSQL_ASSOC))
{
    echo "Author:{$row['javatutorial_author']}  <br> ".
         "Count: {$row['javatutorial_count']} <br> ".
         "Tutorial ID: {$row['javatutorial_id']} <br> ".
         "--------------------------------<br>";
} 
echo "Fetched data successfully\n";
mysql_close($conn);
?>

MySQL LEFT JOIN:

A MySQL left join is different from a simple join. A MySQL LEFT JOIN gives extra consideration to the table that is on the left.
If I do a LEFT JOIN, I get all the records that match in the same way and IN ADDITION I get an extra record for each unmatched record in the left table of the join - thus ensuring (in my example) that every AUTHOR gets a mention:

Example:

Try out the following example to understand LEFT JOIN:

root@host# mysql -u root -p password;
Enter password:*******
mysql> use JAVATUTORIALS;
Database changed
mysql> SELECT a.javatutorial_id, a.javatutorial_author, b.javatutorial_count
    -> FROM javatutorials_tbl a LEFT JOIN javatcount_tbl b
    -> ON a.javatutorial_author = b.javatutorial_author;
+-----------------+---------------------+--------------------+
| javatutorial_id | javatutorial_author | javatutorial_count |
+-----------------+---------------------+--------------------+
|           1     | Kaarhik             |                  1 |
|           2     | Darshan             |               NULL |
|           3     | Gemini              |                  1 |
+-----------------+---------------------+--------------------+
3 rows in set (0.02 sec)

You would need to do more practice to become familiar with JOINS. This is a bit complex concept in MySQL/SQL and will become more clear while doing real examples.

Socializer Widget

SOCIALIZE IT →

Tweet

FOLLOW US →

SHARE IT →