ITSourceCode.com

We Exist to Provide 100% Free Source Code and Tutorials

Custom Search

SQL Join Command

sqltuts

Join Command is basically used to get the data from different tables using a single query into a single table.

A user can use JOIN Command in SELECT, UPDATE and DELETE statements in order to join MySQL tables. The following example is of LEFT JOIN also which is different from simple MySQL JOIN.

Using Joins at Command Prompt:

An Example discussing two tables tcount_table and tutorials_person, in Database TUTORIALS. Following is the code as how to use JOIN command

Example:

  1. root@host# mysql -u root -p password;
  2. Enter password:*******
  3. mysql> use Database TUTORIALS;
  4. Database changed
  5. mysql> SELECT * FROM tcount_table;
  6. +-----------------+----------------+
  7. | tutorial_Person| tutorial_ID |
  8. +-----------------+----------------+
  9. | Rajat | 1 |
  10. | Marut | 2 |
  11. | Karan | 3 |
  12. | Tom | 4 |
  13. | Dick | 5 |
  14. | Harry | NULL|
  15. +-----------------+----------------+
  16. 6 rows in set (0.01 sec)
  17. mysql> SELECT * from tutorials_Person;
  18. +-------------+----------------+-----------------+-----------------+
  19. | tutorial_id | tutorial_Fname | tutorial_Lname | submission_FEE |
  20. +-------------+----------------+-----------------+-----------------+
  21. | 1 | CSS | ram | 200 |
  22. | 2 | HTML5 | shyam | 358 |
  23. | 3 | DBMS | dhanram | 874 |
  24. +-------------+----------------+-----------------+-----------------+
  25. 3 rows in set (0.00 sec)
  26. mysql>

An SQL query to join these two tables.

  1. mysql> SELECT a.tutorial_id, a.tutorial_Fname, b.tutorial_Lname
  2.   -> FROM tutorials_table a, tcount_person b
  3.   -> WHERE a.tutorial_Fname = b.tutorial_Lname;
  4. +-------------+-----------------+----------------+
  5. | tutorial_id | tutorial_Fname | tutorial_Lname |
  6. +-------------+-----------------+----------------+
  7. | 1 | ram | 1 |
  8. | 3 | Dick | 1 |
  9. +-------------+-----------------+----------------+
  10. 2 rows in set (0.01 sec)
  11. mysql>

 

Using Joins in PHP Script:

Example:

Try out the following example:

  1. <?php
  2. $dbhost = 'localhost';
  3. $dbusername = 'root';
  4. $dbpassword = 'rootpassword';
  5. $conn = mysql_connect($dbhost, $dbusername, $dbpassword);
  6. if(! $con )
  7. {
  8.   die('Could not connect: ' . mysql_error());
  9. }
  10. $sql = 'SELECT a.tutorial_id, a.tutorial_Fname, b.tutorial_Lname
  11.  FROM tutorials_table a, tcount_person b
  12.  WHERE a.tutorial_Fname = b.tutorial_Lname';
  13.  
  14. mysql_select_db(' Dtabase TUTORIALS');
  15. $result = mysql_query( $sql, $con );
  16. if(! $result )
  17. {
  18.   die('Could not get data: ' . mysql_error());
  19. }
  20. while($row = mysql_fetch_array($result, MYSQL_ASSOC))
  21. {
  22.   echo "Fname:{$row['tutorial_Fname']} <br> ".
  23.   "Lname: {$row['tutorial_Lname']} <br> ".
  24.   "Tutorial ID: {$row['tutorial_id']} <br> ".
  25.   "--------------------------------<br>";
  26. }
  27. echo " data successfully has been fetched\n";
  28. mysql_close($con);
  29. ?>
Facebook Comments

Check Your Domain Ranking

Leave a Reply

Your email address will not be published. Required fields are marked *

ITSourceCode.com © 2016 Frontier Theme

Subscribe For Latest Updates

Signup for our newsletter and get notified when we publish new articles for free!