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.
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
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
This way we can link three tables and get a meaningful report.
We may be interested to know which are the products not sold or who are the customers who have not purchased today.
We can prepare such reports by using Left Join.
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
