SQL using simple INNER JOIN on 2 Tables Product and ProductDeliveryTBL.
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 |
Below is shown the "ProductDeliveryTBL" table:
Aria Cruz |
Prd003 |
3/21/2011 12:00:00 AM |
Elizabeth Brown |
Prd002 |
9/23/2011 12:00:00 AM |
Maria Larsson |
Prd005 |
9/12/2010 12:00:00 AM |
Martín Sommer |
Prd001 |
7/27/2009 12:00:00 AM |
Peter Franken |
Prd002 |
9/3/2010 12:00:00 AM |
Thomas Hardy |
Prd005 |
6/13/2009 12:00:00 AM |
Yang Wang |
Prd005 |
8/21/2011 12:00:00 AM |
SQL using INNER JOIN
In the "Product" table different products are listed and in the "ProductDeliveryTBL", delivery status and the buyer name is listed.
In the "ProductDeliveryTBL" table ProductID is the Foreign Key.
In the "Product" table ProductID is the Primary Key.
SQL Query will retrieve the product details and the quantity sold.
Text Editor below has the SQL Query, click the "View Result" button, to see the result.
Select ProductDeliveryTBL.ProductID, Product.ProductDesc, Count(ProductDeliveryTBL.ProductID) as NumberSold
From ProductDeliveryTBL
INNER JOIN Product on ProductDeliveryTBL.ProductID = Product.ProductID
GROUP BY ProductDeliveryTBL.ProductID, Product.ProductDesc
ORDER BY ProductDeliveryTBL.ProductID;
Note: Inner Join is used to join both the tables using Primary Key "ProductID" from Product Table
and Foreign Key "ProductID" from ProductDeliveryTBL Table.
Later GROUP BY clause is used to get the count of products.