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: Production.ProductModel

Click to view Full table

Sample table: Production.Product

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.