SQL LINKING TABLE Command
Linking of table is a very common requirement in SQL. Different types of data can be stored in different tables and based on the requirement the tables can be linked to each other and the records can be displayed in a very interactive way.
We can link more than one table to get the records in different combinations as per requirement. Keeping data of one area in one table and linking them each other with key field is better way of designing tables than creating single table with more number of fields. For example in a student database you can keep student contact details in one table and its performance report in another table. You can link these two tables by using one unique student identification number ( ID ).
Let us take one example of linking of tables by considering product and customer relationship. We have a product table where all the records of products are stored. Same way we will have customer table where records of customers are stored. The daily sales keep the record of all the sales. This sales table will keep record of which product who has purchased. So linking is to be done from Sales table to product table and customer table.
This is our customer table
| customer_id | name | address | |
| 1 | Robort John | 123 Avenue, WS | robort@sitename.com |
| 2 | Elena Hick | 567 avenue | elana@sitename.com |
| 3 | Greek Tor | 987 street | greek@sitename.com |
| 4 | Marr Batson | 456 Mary road | marry@sitename.com |
| 5 | Don Rafel | 456 Rafel | don@sitename.com |
| product_id | product_name | product_dtl |
| 1 | CPU | CPU unit details here. |
| 2 | Keyboard | Keyboard Details available here |
| 3 | Mouse | Mouse details available here |
| 4 | Monitor | Monitor details here |
| 5 | CD Drive | CD Drive details here |
| sales_id | product_id | customer_id | date_of_sale |
| 1 | 1 | 2 | 2004-11-12 00:00:00 |
| 2 | 2 | 1 | 2004-01-11 00:00:00 |
| 3 | 3 | 1 | 2004-05-06 00:00:00 |
SELECT product_name, customer.name, date_of_sale
FROM sales, product, customer
WHERE product.product_id = sales.product_id
AND customer.customer_id >= sales.customer_id LIMIT 0, 30| product_name | name | date_of_sale |
| CPU | Elena Hick | 2004-11-12 00:00:00 |
| Keyboard | Robort John | 2004-01-11 00:00:00 |
| Mouse | Robort John | 2004-05-06 00:00:00 |
You can download SQL Dump of these three tables here.
USING LEFT , RIGHT, INNER join of tables
We may be interested to know which are the products not sold or who are the customers who have not purchased.We can prepare such reports by using Left Join , RIGHT Join or INNER JOIN.
Joining tables Using SQL LEFT Join, RIGHT Join and Inner Join
LEFT JOIN ( Basic Query) RIGHT JOIN INNER Join CROSS Join
SQL Union
LEFT JOIN using Multiple Tables Exercise on LEFT JOIN using Product & Sales Tables
Exercise : Sales - Agent using table JOIN and Date functions
Subhendu Mohapatra
Author & Instructor at plus2net
I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.
Connect:
Subscribe to our YouTube Channel here
This article is written by plus2net.com team.
https://www.plus2net.com
| priya sharma | 27-05-2012 |
| dis was one of d finest efforts in all....its g8 nd self explainatory.......acted as saviour a day before my xam. continue with dis job!!!! | |
| Krishna | 11-01-2013 |
| this is nice query and i solved my problem with this..!!!! nice job | |
| Rahul Makwana | 09-04-2013 |
| great explanation.. thanks... | |
| imran khan | 19-04-2013 |
| Superb Explanation ... Saves my day for effort to understand how to link tables through ID | |
| andre3wap | 23-04-2013 |
| Good job man, read it once and got the picture vividly.. | |
| ahmedsomaa | 28-08-2013 |
| how i can select this max(product_id) above query? | |
| smo | 30-08-2013 |
| Same way, Select max(product_id)from product_table. You can read more on sql max here | |
| sanket awasthi | 24-10-2013 |
| good one | |
| Ram | 05-09-2014 |
| This is what i was Googling the Google. Thanks bro | |
| Robin | 11-03-2015 |
| Is there any way to update this sales table automatically by updating order table and customer table accurately in MySQL. Please give me some useful link to understand the concept. Thanks | |
| smo | 13-03-2015 |
| You can join tables and update 2nd table by taking data from first table. | |
| praason@gmail.com | 11-04-2015 |
| great ! | |
| Shreyansh | 19-05-2015 |
| Thanks bro... it really helped a lot... keep up the good work | |
| Shreyansh | 19-05-2015 |
| hey could you explain that query a little bit... actually i am new to mysql | |
| lawal victor | 26-07-2015 |
| nice one, it solved my problem. more wisdom to you | |
| John Jerald | 16-10-2015 |
| hi sir,i am a newbie in mysql database, i create two tables customer and orders, can i ask if you join the two tables field is it required to create new table for the joining fields?please help me.. thank you | |
| MD KASIM ALI | 10-11-2015 |
| NICE EXAMPLE, THANK YOU | |
| kumbi | 28-03-2016 |
| Parse error: syntax error, unexpected 'product_name' (T_STRING) in C:xampphtdocsregmultiple.php on line 28 | |
| adams majid | 06-04-2016 |
| good job...can u explain how i can implement such comment box on my website thanks... | |
| smo1234 | 07-04-2016 |
| You can read the comment posting tutorial here. This script is modified further and used here. | |
| jyoti | 20-06-2016 |
| its solve the some problem but how to coding in java | |
30-03-2020 | |
| this is a great tutorial, especially the diagram at the top. that diagram only was sufficient to make me, an almost-complete sql noob, grasp how sql table linking works and why it's so useful, and, most importantly of all, how I could use it to implement my data structure | |
SQL Video Tutorials