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 email
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 table is below
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 table is here below.
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

From these three tables let us find out the information on sales by linking these tables.  We will look into sales table and link it to the customer table by the customer id field and in same way we will link product table by product ID field. We will use WHERE sql command to link different tables. Here is the command to link three tables.
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
The above SQL command links three tables and display the required result. The tables are linked by their ID fields. The output is here
product_name name date_of_sale
CPUElena Hick2004-11-12 00:00:00
KeyboardRobort John2004-01-11 00:00:00
MouseRobort John2004-05-06 00:00:00
This way we can link three tables and get a meaningful report.
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

Subscribe

* indicates required
Subscribe to plus2net

    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

    Post your comments , suggestion , error , requirements etc here





    SQL Video Tutorials










    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2021 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer