In the previous MySQL tutorial, you learnt how to sort the data using “where”, “like”, “order by” and “null” clauses. In this tutorial, you will learn how to display the output by joining data of two tables.
Suppose we have two tables; “office1” having three columns as “empid”,”name”,”salary” and office2 having three columns as “empid”,”name” and “designation”. These are shown below:
You want to know the designation of employees when you know their employee ids. You can see the result only by joining the two tables.
For joining two tables, there should a similar value between the two tables which can serve as the primary key and on the basis of which two tables can be joined.
The syntax for joining two tables is:
Select table1.column1, table2.column2 from table1,table2 where table1.column1=table2.column1;
Where column1 is a column within table1 and column2 is within table2. Here “.” is used to access the particular columns from specific tables.
This mysql query will select values of column1 from table1 and the values of column2 from table2 but only that values where column1 matches with that of column2 of table2.
Example,
Select office1.empid,office2.empid from office1,office2 where office1.empid=office2.empid;
The output is as follows:
Using PHP:
<?php
$link=mysql_connect('localhost','root','your_password');
mysql_select_db('mysql');
echo "connected to mysql<br>";
$q="select office1.name,office2.designation from office1,office2 where office1.empid=office2.empid";
$r=mysql_query($q);
while($ro=mysql_fetch_array($r))
{
echo "name:".$ro['name']."\n";
echo "designation:"."\n".$ro['designation']."<br>";
}
?>
The output is:
Now that you know how to join two tables, continue with the next tutorial on how to import and export databases in MySQL. Also, if you have any queries, please leave a comment below.
