w3resource

AdventureWorks Database: Using a derived table with multiple values

SQL Query - AdventureWorks: Exercise-45 with Solution

45. Write a SQL query to find the productid, name, and colour of the items 'Blade', 'Crown Race' and 'AWC Logo Cap' using a derived table with multiple values.

Sample table: Production.Product


Click to view Full table

Sample Solution:

SELECT ProductID, a.Name, Color  
FROM Production.Product AS a  
INNER JOIN (VALUES ('Blade'), ('Crown Race'), ('AWC Logo Cap')) AS b(Name)   
ON a.Name = b.Name;

Sample Output:

productid|name        |color|
---------+------------+-----+
      316|Blade       |     |
      323|Crown Race  |     |
      712|AWC Logo Cap|Multi|

SQL AdventureWorks Editor:

Practice Online


Contribute your code and comments through Disqus.

Previous: Retrieve individuals using multiple conditions.
Next: Each salesperson's annual sales orders.

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.