Practical Use of Left Join php mysql

Practical Use of Left Join php mysql

Some companies will be given trainings to their staff members.In this case they maintain data base with 2 tables one with staff members details and other with trainings details.when all staff members are not trained higher executives need to know the staff members who have not trained yet.Here with this problem left joining of 2 tables in following manner executives could be easily found the staff members have not trained yet.

I have used bellow 2 tables for this purpose.

CREATE TABLE IF NOT EXISTS `dept_division` (   `id` int(24) NOT NULL AUTO_INCREMENT,   `name` varchar(45) NOT NULL,   `age` varchar(46) NOT NULL,   `division` varchar(45) NOT NULL,   PRIMARY KEY (`id`) ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
CREATE TABLE IF NOT EXISTS `training` (   `id` int(40) NOT NULL AUTO_INCREMENT,   `traning_id` int(40) NOT NULL,   `training_obtained` varchar(40) NOT NULL,   PRIMARY KEY (`id`) ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

According to these two tables you can see jennifer,sofia & wictor have not got any training. dept_division table "id" is used to identify the staff members in table training as "training_id".
Create following php file and run it and you get the expected result.

<?php $host="localhost"; $username="root"; $password=""; $db_name="my_database"; mysql_connect("$host", "$username", "$password")or die("cannot connect"); mysql_select_db("$db_name")or die("cannot select DB"); $sql ="SELECT dept_division.name, dept_division.age,dept_division.division FROM dept_division     LEFT JOIN  training ON training.traning_id=dept_division.id WHERE training.traning_id IS NULL";      $result=mysql_query($sql); ?> <table  align="center"  bgcolor="#15475c"> <tr bgcolor="#ddeef5"> <td align="center" ><strong>Name</strong></td> <td align="center" "><strong>Age</strong></td> <td align="center" "><strong>Division</strong></td> </tr> <?php while($rows=mysql_fetch_array($result)){ ?> <tr bgcolor="#d0e7f1"> <td><?php echo $rows['name']; ?></td> <td><?php echo $rows['age']; ?></td> <td><?php echo $rows['division']; ?></td> </tr> <?php } ?> </table>

Resulting table is

Name	Age	Division
jennifer	23	Production
sofia	25	service
wictor	45	production