PHP MySQL tutorial

INNER JOIN in MySQL

Till now, we have been retrieving data from a single table. However, in relational databases such as MySQL tables are related with each other via some common field. Take an example of a ‘patient’ and ‘department’ table in the hospital database. A patient is admitted to a specific department of the hospital on the other hand a department can have many patients admitted in it. In such cases, the relational between the two columns is defined via some common field.

For instance, the relationship between patient and department table can be established by adding a dep_id column in both the tables. This dep_id column will serve as primary key for the department table, and for the patient table this dep_id will serve as foreign key.

Now, let’s come to our topic, which is JOIN in MySQL. JOIN statement is used to retrieve data simultaneously from two or more than two tables based on some common field between them. Suppose, we have patient and department tables which have following data.

 

Patient Table

 

patient_id patient_fname patient_lname patient_age dep_id
1 Mike Getting 20 3
2 Sara Taylor 25 2
3 Vince James 52 9
4 Shawn Been 45 6
5 Lara Bran 43 7
6 Fred Vicks 8 5
7 Pam Beesly 38 8

 

Department Table

 

dep_id dep_name dep_capacity
2 Neurology 200
3 Cardiology 300
4 Nephrology 200
5 Children 100
6 Pythology 400
7 Optician 500

 

You can see from the ‘Patient’ and ‘Department’ tables that they both have dep_id field common, so now if we use INNER join to retrieve data from both the columns, only those records will be retrieved where there exist common values for dep_id in both the ‘Patient’ and ‘Department’ tables. Have a look at the following examples.

 




Implementing INNER JOIN



connect_error) {
    die("Connection not established: " . $connection->connect_error);
} 
    
$query = "Use Hospital;";
$connection->query($query);
    
// Implementing INNER JOIN

$query = "SELECT patient.patient_fname, patient.patient_age, department.dep_name ".
    "FROM patient INNER JOIN department ".
    "ON patient.dep_id = department.dep_id";
     
$output = $connection->query($query);

if ($output->num_rows > 0) {
    // output data of each row
    while($row = $output->fetch_assoc()) {
        echo "First Name: " . $row["patient_fname"]. " - Age: " . $row["patient_age"]. " - Department: " . $row["dep_name"]. "
"; } } else { echo "0 results"; } $connection->close(); ?>

 

Download the code

To select data from multiple table, you have to prefix the table name with the column you want to retrieve using the dot operator. The output of the above code will look like this.

 

First Name: Mike – Age: 20 – Department: Cardiology
First Name: Sara – Age: 25 – Department: Neurology
First Name: Shawn – Age: 45 – Department: Pythology
First Name: Lara – Age: 43 – Department: Optician
First Name: Fred – Age: 8 – Department: Children

 

You can see that only those columns have been retrieved where there was a common value for dep_id column in both the tables. The record for Vince and Pam has not been retrieved because the values for their dep_id column did not exist in the dep_id column of department table.

<<< Implementing Check on Table ColumnsLEFT JOIN in MySQL >>>
Copyright 2005-2016 KnowledgeHills. Privacy Policy. Contact .