w3resource

AdventureWorks Database: Combine ProductModelID and Name columns from two tables

SQL Query - AdventureWorks: Exercise-137 with Solution

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.BusinessEntity
businessentityid|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 --

Click to view Full table

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 --

Click to view Full table

Sample Solution:

SELECT ProductID, Name  
FROM Production.Product  
WHERE ProductID NOT IN (3, 4)  
UNION  
SELECT ProductModelID, Name  
FROM Production.ProductModel   
ORDER 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.



Follow us on Facebook and Twitter for latest update.