AdventureWorks Database: A JOIN clause can join multiple values

SQL Query - AdventureWorks: Exercise-163 with Solution

163. From the following table, write a query in SQL to join the Product table with a set of specific product names using a JOIN clause. Return the columns ProductID, Name, and Color. The query should filter the Product table to include only those products with the names 'Blade', 'Crown Race', and 'AWC Logo Cap'.

productid|name                            |productnumber|makeflag|finishedgoodsflag|color       |safetystocklevel|reorderpoint|standardcost|listprice|size|sizeunitmeasurecode|weightunitmeasurecode|weight |daystomanufacture|productline|class|style|productsubcategoryid|productmodelid|sellstartdate          |sellenddate            |discontinueddate|rowguid                             |modifieddate           |
---------+--------------------------------+-------------+--------+-----------------+------------+----------------+------------+------------+---------+----+-------------------+---------------------+-------+-----------------+-----------+-----+-----+--------------------+--------------+-----------------------+-----------------------+----------------+------------------------------------+-----------------------+
        1|Adjustable Race                 |AR-5381      |false   |false            |            |            1000|         750|           0|        0|    |                   |                     |       |                0|           |     |     |                    |              |2008-04-30 00:00:00.000|                       |                |694215b7-08f7-4c0d-acb1-d734ba44c0c8|2014-02-08 10:01:36.827|
        2|Bearing Ball                    |BA-8327      |false   |false            |            |            1000|         750|           0|        0|    |                   |                     |       |                0|           |     |     |                    |              |2008-04-30 00:00:00.000|                       |                |58ae3c20-4f3a-4749-a7d4-d568806cc537|2014-02-08 10:01:36.827|
        3|BB Ball Bearing                 |BE-2349      |true    |false            |            |             800|         600|           0|        0|    |                   |                     |       |                1|           |     |     |                    |              |2008-04-30 00:00:00.000|                       |                |9c21aed2-5bfa-4f18-bcb8-f11638dc2e4e|2014-02-08 10:01:36.827|
        4|Headset Ball Bearings           |BE-2908      |false   |false            |            |             800|         600|           0|        0|    |                   |                     |       |                0|           |     |     |                    |              |2008-04-30 00:00:00.000|                       |                |ecfed6cb-51ff-49b5-b06c-7d8ac834db8b|2014-02-08 10:01:36.827|
      316|Blade                           |BL-2036      |true    |false            |            |             800|         600|           0|        0|    |                   |                     |       |                1|           |     |     |                    |              |2008-04-30 00:00:00.000|                       |                |e73e9750-603b-4131-89f5-3dd15ed5ff80|2014-02-08 10:01:36.827|
      317|LL Crankarm                     |CA-5965      |false   |false            |Black       |             500|         375|           0|        0|    |                   |                     |       |                0|           |L    |     |                    |              |2008-04-30 00:00:00.000|                       |                |3c9d10b7-a6b2-4774-9963-c19dcee72fea|2014-02-08 10:01:36.827|
      318|ML Crankarm                     |CA-6738      |false   |false            |Black       |             500|         375|           0|        0|    |                   |                     |       |                0|           |M    |     |                    |              |2008-04-30 00:00:00.000|                       |                |eabb9a92-fa07-4eab-8955-f0517b4a4ca7|2014-02-08 10:01:36.827|
	  -- more --

Click to view Full table

Sample Solution:

-- Selecting ProductID, Name, and Color columns
SELECT 
    ProductID, 
    a.Name, 
    Color  
-- From the Product table aliased as 'a' and performing an inner join with a set of values specified in the VALUES clause, aliased as 'b', with a single column 'Name'
FROM 
    Production.Product AS a  
INNER JOIN 
    (VALUES ('Blade'), ('Crown Race'), ('AWC Logo Cap')) AS b(Name)   
-- Joining condition specifying that the 'Name' column from the Product table should match with the 'Name' column from the values provided in the VALUES clause
ON 
    a.Name = b.Name;

Explanation:

• This SQL code retrieves the ProductID, Name, and Color columns from the Product table.
• The SELECT statement specifies the columns to be included in the result set.
• The FROM clause indicates the table involved in the query, which is the Product table, aliased as 'a'.
• The INNER JOIN clause is used to join the Product table with a set of values provided in the VALUES clause. This set of values is aliased as 'b' and contains three rows with the values 'Blade', 'Crown Race', and 'AWC Logo Cap'.
• The ON clause specifies the condition for the join, where the 'Name' column from the Product table ('a.Name') should match with the 'Name' column from the values provided in the VALUES clause ('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: Get mailing addresses for companies in cities begin with PA outside US.
Next: Fetch columns using multiple CTE in a single query.