A detailed tutorial on joining operations using join statements in Mysql _mysql

Source: Internet
Author: User
Tags joins learn php

So far, we've learned to get data from a table. It is simple to need, but in most realistic use of MySQL, it is often necessary to put data from multiple tables in a single query.

You can use a single SQL query from multiple tables. The join behavior in MySQL means that two or more tables into a table can use joins to join the MySQL table in the Select,update and DELETE statements. We'll see an example of a left join with a simple MySQL connection.
to use joins at the command prompt:

Suppose the complete list of our two-table tutorials Tcount_tbl and Tutorials_tbl is as follows:
Example:

Try the following example:

root@host# mysql-u root-p password;
Enter password:******* mysql> Use tutorials;
Database changed mysql> SELECT * from TCOUNT_TBL; +-----------------+----------------+
| Tutorial_author |
Tutorial_count | +-----------------+----------------+
|       Mahran | 20 | |      Mahnaz | NULL | |      Jen | NULL | |       Gill | 20 | |       John Poul | 1 | |       Sanjay |
1 |
+-----------------+----------------+ 6 rows in Set (0.01 sec) mysql> SELECT * from TUTORIALS_TBL; +-------------+----------------+-----------------+-----------------+
| tutorial_id | Tutorial_title | Tutorial_author |
Submission_date |      +-------------+----------------+-----------------+-----------------+
| 1 | Learn PHP | John Poul |      2007-05-24 | | 2 | Learn MySQL | Abdul S |      2007-05-24 | | 3 | JAVA Tutorial | Sanjay |
2007-05-06 |

 +-------------+----------------+-----------------+-----------------+ 3 rows in Set (0.00 sec) mysql>

Now, by joining these two tables, we can write a SQL query. This query selects all the authors from the table TUTORIALS_TBL and will pick up the corresponding number of tutorials from TCOUNT_TBL.

Mysql> SELECT a.tutorial_id, A.tutorial_author, B.tutorial_count
  -> from Tutorials_tbl A, tcount_tbl b
  -& Gt 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>

To use joins in PHP scripts:

You can use any of the above SQL queries for PHP scripts. You only need to execute the SQL query through the PHP function mysql_query (), and then get the results in the normal way.
Example:

Try 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.tutorial_id, A.tutorial_author, b.tutorial_count from
    tutorials_tbl A, tcount_tbl b
    WHERE A.tutorial_author = B.tutorial_author ';

mysql_select_db (' tutorials ');
$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 [' Tutorial_author ']} <br> ".
     " Count: {$row [' Tutorial_count ']} <br> '.
     ' Tutorial ID: {$row [' tutorial_id ']} <br> '.
     ' --------------------------------<br> ";
} 
echo "fetched data successfully\n";
Mysql_close ($conn);
? >

MySQL Left connection:

A simple connection and a MySQL left connection are different. A MySQL left join provides extra consideration to the table on the left-hand side.

If you do a LEFT join, all of the records that you get are matched in the same way, and you get an extra record of each mismatched record, a join in the left-hand table-thus ensuring that each author is associated (in this example):
Instance:

Try the following example to understand the left JOIN:

root@host# mysql-u root-p password;
Enter password:*******
mysql> use tutorials;
Database changed
mysql> SELECT a.tutorial_id, A.tutorial_author, B.tutorial_count-> from
  tutorials_tbl A left JOIN tcount_tbl b
  -> on a.tutorial_author = B.tutorial_author;
+-------------+-----------------+----------------+
| tutorial_id | tutorial_author | Tutorial_count
| +-------------+-----------------+----------------+
|      1 | John Poul    |       1 |
|      2 | Abdul S     |      NULL |
|      3 | Sanjay     |       1 |
+-------------+-----------------+----------------+
3 rows in Set (0.02 sec)

More practice is needed to become familiar with joins. This is a complex concept that will become clearer in the mysql/sql.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.