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


What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Share this Tutorial / Exercise on : Facebook and Twitter

SQL: Tips of the Day

Convert Timestamp to date in MySQL Query:

DATE_FORMAT(FROM_UNIXTIME(`user.registration`), '%e %b %Y') AS 'date_formatted'

Database: MySQL

Ref : https://bit.ly/3EJPnMQ