Here we are going to use 2 Tables "ProductDeliveryTBL" and "Product".
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", Column ProductID is the Primary Key.
The "ProductDeliveryTBL" table data, the ProductDesc and ProductPrice is not available. It is available in the "Product" table.
Using the Subquery we can make that available in the SQL resultset, using the Column ProductID, which is common in both the tables.
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 PDLTbl.ProductID, PDLTbl.CustomerName,
(Select ProductDesc from Product where Product.ProductID = PDLTbl.ProductID) ProductDescription,
(Select ProductPrice from Product where Product.ProductID = PDLTbl.ProductID) ProductPrice
From ProductDeliveryTBL PDLTbl;
Note: Here the PDLTbl.ProductID is passed as the argument to the Subquery. For every ProductID in the ProductDeliveryTBL,
the correct ProductDesc and ProductPrice is retrieved.