|
| |
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.
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 out put 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 mining full report. We can use left join sql command also to link tables.
| |
|
|
|
