AdventureWorks Database: Fetch the matching rows from two tables
SQL Query - AdventureWorks: Exercise-166 with Solution
166. From the following tables write a query in SQL to return the SalesOrderNumber, ProductKey, and EnglishProductName columns.
Sample table: Sales.SalesOrderDetail
salesorderid|salesorderdetailid|carriertrackingnumber|orderqty|productid|specialofferid|unitprice|unitpricediscount|rowguid |modifieddate |
------------+------------------+---------------------+--------+---------+--------------+---------+-----------------+------------------------------------+-----------------------+
43659| 1|4911-403C-98 | 1| 776| 1| 2024.994| 0|b207c96d-d9e6-402b-8470-2cc176c42283|2011-05-31 00:00:00.000|
43659| 2|4911-403C-98 | 3| 777| 1| 2024.994| 0|7abb600d-1e77-41be-9fe5-b9142cfc08fa|2011-05-31 00:00:00.000|
43659| 3|4911-403C-98 | 1| 778| 1| 2024.994| 0|475cf8c6-49f6-486e-b0ad-afc6a50cdd2f|2011-05-31 00:00:00.000|
43659| 4|4911-403C-98 | 1| 771| 1| 2039.994| 0|04c4de91-5815-45d6-8670-f462719fbce3|2011-05-31 00:00:00.000|
43659| 5|4911-403C-98 | 1| 772| 1| 2039.994| 0|5a74c7d2-e641-438e-a7ac-37bf23280301|2011-05-31 00:00:00.000|
43659| 6|4911-403C-98 | 2| 773| 1| 2039.994| 0|ce472532-a4c0-45ba-816e-eefd3fd848b3|2011-05-31 00:00:00.000|
43659| 7|4911-403C-98 | 1| 774| 1| 2039.994| 0|80667840-f962-4ee3-96e0-aeca108e0d4f|2011-05-31 00:00:00.000|
43659| 8|4911-403C-98 | 3| 714| 1| 28.8404| 0|e9d54907-e7b7-4969-80d9-76ba69f8a836|2011-05-31 00:00:00.000|
43659| 9|4911-403C-98 | 1| 716| 1| 28.8404| 0|aa542630-bdcd-4ce5-89a0-c1bf82747725|2011-05-31 00:00:00.000|
43659| 10|4911-403C-98 | 6| 709| 1| 5.7| 0|ac769034-3c2f-495c-a5a7-3b71cdb25d4e|2011-05-31 00:00:00.000|
43659| 11|4911-403C-98 | 2| 712| 1| 5.1865| 0|06a66921-6b9f-4199-a912-ddafd383472b|2011-05-31 00:00:00.000|
43659| 12|4911-403C-98 | 4| 711| 1| 20.1865| 0|0e371ee3-253e-4bb0-b813-83cf4224f972|2011-05-31 00:00:00.000|
-- more --
Sample table: Production.Product
productid|name |productnumber|makeflag|finishedgoodsflag|color |safetystocklevel|reorderpoint|standardcost|listprice|size|sizeunitmeasurecode|weightunitmeasurecode|weight |daystomanufacture|productline|class|style|productsubcategoryid|productmodelid|sellstartdate |sellenddate |discontinueddate|rowguid |modifieddate |
---------+--------------------------------+-------------+--------+-----------------+------------+----------------+------------+------------+---------+----+-------------------+---------------------+-------+-----------------+-----------+-----+-----+--------------------+--------------+-----------------------+-----------------------+----------------+------------------------------------+-----------------------+
1|Adjustable Race |AR-5381 |false |false | | 1000| 750| 0| 0| | | | | 0| | | | | |2008-04-30 00:00:00.000| | |694215b7-08f7-4c0d-acb1-d734ba44c0c8|2014-02-08 10:01:36.827|
2|Bearing Ball |BA-8327 |false |false | | 1000| 750| 0| 0| | | | | 0| | | | | |2008-04-30 00:00:00.000| | |58ae3c20-4f3a-4749-a7d4-d568806cc537|2014-02-08 10:01:36.827|
3|BB Ball Bearing |BE-2349 |true |false | | 800| 600| 0| 0| | | | | 1| | | | | |2008-04-30 00:00:00.000| | |9c21aed2-5bfa-4f18-bcb8-f11638dc2e4e|2014-02-08 10:01:36.827|
4|Headset Ball Bearings |BE-2908 |false |false | | 800| 600| 0| 0| | | | | 0| | | | | |2008-04-30 00:00:00.000| | |ecfed6cb-51ff-49b5-b06c-7d8ac834db8b|2014-02-08 10:01:36.827|
316|Blade |BL-2036 |true |false | | 800| 600| 0| 0| | | | | 1| | | | | |2008-04-30 00:00:00.000| | |e73e9750-603b-4131-89f5-3dd15ed5ff80|2014-02-08 10:01:36.827|
317|LL Crankarm |CA-5965 |false |false |Black | 500| 375| 0| 0| | | | | 0| |L | | | |2008-04-30 00:00:00.000| | |3c9d10b7-a6b2-4774-9963-c19dcee72fea|2014-02-08 10:01:36.827|
318|ML Crankarm |CA-6738 |false |false |Black | 500| 375| 0| 0| | | | | 0| |M | | | |2008-04-30 00:00:00.000| | |eabb9a92-fa07-4eab-8955-f0517b4a4ca7|2014-02-08 10:01:36.827|
-- more --
Sample Solution:
-- This SQL query selects the SalesOrderID from the salesorderdetail table and the ProductID and Name from the product table, joining the two tables based on the ProductID column.
-- Selecting SalesOrderID from the salesorderdetail table and assigning the alias 'fis' to the table.
SELECT fis.SalesOrderid,
-- Selecting ProductID from the product table and assigning the alias 'dp' to the table.
dp.Productid,
-- Selecting the Name column from the product table.
dp.Name
-- Joining the salesorderdetail table (aliased as 'fis') with the product table (aliased as 'dp') using INNER JOIN and matching rows where the ProductID in both tables are equal.
FROM sales.salesorderdetail AS fis
INNER JOIN production.product AS dp
ON dp.Productid = fis.Productid;
Explanation:
- The query retrieves the SalesOrderID, ProductID, and Name of each product ordered.
- An INNER JOIN is used to combine rows from the salesorderdetail table (aliased as 'fis') with matching rows from the product table (aliased as 'dp') based on the ProductID column.
- This join ensures that only rows with matching ProductID values in both tables are included in the result set.
Or
-- This SQL query selects the SalesOrderID from the salesorderdetail table and the ProductID and Name from the product table, joining the two tables based on the ProductID column.
-- Selecting SalesOrderID from the salesorderdetail table and assigning the alias 'fis' to the table.
SELECT fis.SalesOrderid,
-- Selecting ProductID from the product table and assigning the alias 'dp' to the table.
dp.Productid,
-- Selecting the Name column from the product table.
dp.Name
-- Joining the salesorderdetail table (aliased as 'fis') with the product table (aliased as 'dp') using INNER JOIN and matching rows where the ProductID in both tables are equal.
FROM sales.salesorderdetail AS fis
JOIN production.product AS dp
ON dp.Productid = fis.Productid;
Explanation:
- The query retrieves the SalesOrderID, ProductID, and Name of each product ordered.
- An INNER JOIN is used to combine rows from the salesorderdetail table (aliased as 'fis') with matching rows from the product table (aliased as 'dp') based on the ProductID column.
- This join ensures that only rows with matching ProductID values in both tables are included in the result set.
Sample Output:
salesorderid|productid|name |
------------+---------+--------------------------------+
43659| 776|Mountain-100 Black, 42 |
43659| 777|Mountain-100 Black, 44 |
43659| 778|Mountain-100 Black, 48 |
43659| 771|Mountain-100 Silver, 38 |
43659| 772|Mountain-100 Silver, 42 |
43659| 773|Mountain-100 Silver, 44 |
43659| 774|Mountain-100 Silver, 48 |
43659| 714|Long-Sleeve Logo Jersey, M |
43659| 716|Long-Sleeve Logo Jersey, XL |
43659| 709|Mountain Bike Socks, M |
43659| 712|AWC Logo Cap |
43659| 711|Sport-100 Helmet, Blue |
43660| 762|Road-650 Red, 44 |
43660| 758|Road-450 Red, 52 |
43661| 745|HL Mountain Frame - Black, 48 |
...
SQL AdventureWorks Editor:
Practice Online
Contribute your code and comments through Disqus.
Previous: Cross product of BusinessEntityID and Department columns.
Next: Return all orders with IDs greater than 60000.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.
https://www.w3resource.com/sql-exercises/adventureworks/sql-adventureworks-exercise-166.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics