Here we are going to Join 2 Tables and Retrieve the data.
There are two Tables "ProductDeliveryTBL" and "Product", these two tables will be joined
using the Inner Join Clause.
Below is shown the "ProductDeliveryTBL" table:
Aria Cruz |
Prd003 |
3/21/2011 12:00:00 AM |
NotDelivered |
Elizabeth Brown |
Prd002 |
9/23/2011 12:00:00 AM |
Delivered |
Maria Larsson |
Prd005 |
9/12/2010 12:00:00 AM |
NotDelivered |
Martín Sommer |
Prd001 |
7/27/2009 12:00:00 AM |
Delivered |
Peter Franken |
Prd002 |
9/3/2010 12:00:00 AM |
Delivered |
Thomas Hardy |
Prd005 |
6/13/2009 12:00:00 AM |
Delivered |
Yang Wang |
Prd005 |
8/21/2011 12:00:00 AM |
Delivered |
Below is shown the "Product" table:
Prd001 |
DVD Player |
23 |
85.50 |
Prd002 |
Mobile Phone |
45 |
70.33 |
Prd003 |
Laptop |
15 |
250.45 |
Prd004 |
Watch |
42 |
33.45 |
Prd005 |
Camera |
34 |
95.75 |
In the table named "ProductDeliveryTBL" , Column ProductID is the Foreign Key.
In the table named "Product" table, Column ProductID is the Primary Key.
The data about ProductDesc and ProductPrice is not available in "ProductDeliveryTBL" table. It is available in the "Product" table.
To get the Product Delivery data along with ProductDesc and ProductPrice using the SQL, we have to use the column "ProductID",
which is a common column in both the tables. Need to use this column "ProductID" to do Inner Join between the tables
"ProductDeliveryTBL" and "Product".
The following SQL query acheives the above said task.
Text Editor below has the SQL Query, click the "View Result" button, to see the result.
Select ProductDeliveryTBL.CustomerName, ProductDeliveryTBL.ProductID, Product.ProductDesc, Product.ProductPrice, ProductDeliveryTBL.DeliveryStatus
From ProductDeliveryTBL
Inner Join Product on Product.ProductID = ProductDeliveryTBL.ProductID
Note: For every ProductID in the ProductDeliveryTBL, the correct ProductDesc and ProductPrice is retrieved.