SQL PHP HTML ASP JavaScript articles and free scripts to download


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.  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 do 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. We can use left join sql command also to link tables.

Number of User Comments : 11


priya sharma27-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!!!!
this is nice query and i solved my problem with this..!!!! nice job
Rahul Makwana09-04-2013
great explanation.. thanks...
imran khan19-04-2013
Superb Explanation ... Saves my day for effort to understand how to link tables through ID
Good job man, read it once and got the picture vividly..
how i can select this max(product_id) above query?
Same way,
Select max(product_id)from product_table.
You can read more on <a href=http://www.plus2net.com/sql_tutorial/sql_max.php>sql max</a> here
sanket awasthi24-10-2013
good one
This is what i was Googling the Google. Thanks bro
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
Post Comment This is for short comments only. Use the forum for more discussions.
Email( not to be displayed)Privacy Policy
1+2=This is to prevent automatic submission by spammers. Please enter the result of the sum as asked

HTML . MySQL. PHP. JavaScript. ASP. Photoshop. Articles. FORUM Contact us

©2000-2015 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer