AdventureWorks Database: Combine ProductModelID and Name columns from two tables
137. From the following tables write a query in SQL to combine the ProductModelID and Name columns. A result set includes columns for productid 3 and 4. Sort the results by name ascending.
Sample table: Person.BusinessEntitybusinessentityid|rowguid |modifieddate | ----------------+------------------------------------+-----------------------+ 1|0c7d8f81-d7b1-4cf0-9c0a-4cd8b6b50087|2017-12-13 13:20:24.150| 2|6648747f-7843-4002-b317-65389684c398|2017-12-13 13:20:24.430| 3|568204da-93d7-42f4-8a7a-4446a144277d|2017-12-13 13:20:24.540| 4|0eff57b9-4f4f-41a6-8867-658c199a5fc0|2017-12-13 13:20:24.570| 5|b82f88d1-ff79-4fd9-8c54-9d24c140f647|2017-12-13 13:20:24.633| 6|1b3d077a-1941-4d6e-8328-f7dc03595565|2017-12-13 13:20:24.680| 7|c1898370-a36f-43a2-987c-0bf24fe3fb82|2017-12-13 13:20:24.727| 8|2b50abb8-abab-412b-a4d0-4fd5ebeb5cbe|2017-12-13 13:20:24.773| 9|5c0ab449-a087-4d8d-834f-3726061b6bfa|2017-12-13 13:20:24.803| 10|0f3cc1d7-f484-4bde-b088-b11ef03e2f52|2017-12-13 13:20:24.850| 11|a417a3d1-00eb-4d7f-b793-f93dc2c5391d|2017-12-13 13:20:24.900| 12|ebd8a50f-322e-4426-a39a-566fd5535b1c|2017-12-13 13:20:24.947| -- more --Sample table: Person.Person
businessentityid|persontype|namestyle|title|firstname |middlename |lastname |suffix|emailpromotion|additionalcontactinfo|demographics|rowguid |modifieddate | ----------------+----------+---------+-----+------------------------+----------------+----------------------+------+--------------+---------------------+------------+------------------------------------+-----------------------+ 1|EM |false | |Ken |J |Sánchez | | 0| |[XML] |92c4279f-1207-48a3-8448-4636514eb7e2|2009-01-07 00:00:00.000| 2|EM |false | |Terri |Lee |Duffy | | 1| |[XML] |d8763459-8aa8-47cc-aff7-c9079af79033|2008-01-24 00:00:00.000| 3|EM |false | |Roberto | |Tamburello | | 0| |[XML] |e1a2555e-0828-434b-a33b-6f38136a37de|2007-11-04 00:00:00.000| 4|EM |false | |Rob | |Walters | | 0| |[XML] |f2d7ce06-38b3-4357-805b-f4b6b71c01ff|2007-11-28 00:00:00.000| 5|EM |false |Ms. |Gail |A |Erickson | | 0| |[XML] |f3a3f6b4-ae3b-430c-a754-9f2231ba6fef|2007-12-30 00:00:00.000| 6|EM |false |Mr. |Jossef |H |Goldberg | | 0| |[XML] |0dea28fd-effe-482a-afd3-b7e8f199d56f|2013-12-16 00:00:00.000| 7|EM |false | |Dylan |A |Miller | | 2| |[XML] |c45e8ab8-01be-4b76-b215-820c8368181a|2009-02-01 00:00:00.000| 8|EM |false | |Diane |L |Margheim | | 0| |[XML] |a948e590-4a56-45a9-bc9a-160a1cc9d990|2008-12-22 00:00:00.000| 9|EM |false | |Gigi |N |Matthew | | 0| |[XML] |5fc28c0e-6d36-4252-9846-05caa0b1f6c5|2009-01-09 00:00:00.000| -- more --
Sample Solution:
-- Selecting ProductID and Name columns from the Product table where ProductID is not 3 or 4, and combining the result with the ProductModelID and Name columns from the ProductModel table using the UNION operator
SELECT
-- Selecting the ProductID column and Name column from the Product table
ProductID,
Name
-- Selecting data from the Product table, filtering out ProductID values 3 and 4
FROM
Production.Product
-- Filtering records where ProductID is not 3 or 4
WHERE
ProductID NOT IN (3, 4)
-- Combining the results with the following SELECT statement using the UNION operator
UNION
-- Selecting the ProductModelID column and Name column from the ProductModel table
SELECT
ProductModelID,
Name
-- Selecting data from the ProductModel table
FROM
Production.ProductModel
-- Ordering the result set by Name
ORDER BY
Name;
Explanation:
- This SQL code retrieves ProductID and Name columns from the Product table where ProductID is not 3 or 4, and combines the result with the ProductModelID and Name columns from the ProductModel table using the UNION operator.
- The first SELECT statement selects ProductID and Name columns from the Product table, filtering out ProductID values 3 and 4.
- The WHERE clause filters records where ProductID is not 3 or 4.
- The UNION operator combines the results of the first SELECT statement with the results of the second SELECT statement, ensuring that duplicate rows are removed.
- The second SELECT statement selects ProductModelID and Name columns from the ProductModel table.
- The ORDER BY clause orders the combined result set by Name.
Sample Output:
productid|name | ---------+--------------------------------+ 1|Adjustable Race | 122|All-Purpose Bike Stand | 879|All-Purpose Bike Stand | 712|AWC Logo Cap | 2|Bearing Ball | 119|Bike Wash | 877|Bike Wash - Dissolver | 316|Blade | 115|Cable Lock | 843|Cable Lock | 98|Chain | 952|Chain | 324|Chain Stays | 322|Chainring | ...
SQL AdventureWorks Editor:
Practice Online
Contribute your code and comments through Disqus.
Previous: Any distinct businessentityid from the first query not found in the second query.
Next: Add vacation and sick time to find total hours away from work.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics