SQL Join Command

0
49

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
(Visited 57 times, 1 visits today)
SHARE
Previous articleSQL AND & OR Operators
Next articleSQL NULL Values
Hello Itsourcecoders, welcome to itsourcecode.com. I'm Joken Villanueva, MIT a passionate Blogger, Programmer and a Hobbyist. I started Itsourcecode because I wanted to give back and Share all the learnings and knowledge I've learned in my career and I believe through this website I would be able to help and assist those newbie programmers in enhancing their skills from different programming languages. So let us all help each other by sharing our ideas!

LEAVE A REPLY

Please enter your comment!
Please enter your name here