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.