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: Person.BusinessEntity
businessentityid|rowguid                             |modifieddate           |
----------------+------------------------------------+-----------------------+
               1|0c7d8f81-d7b1-4cf0-9c0a-4cd8b6b50087|2017-12-13 13:20:24.150|
               2|6648747f-7843-4002-b317-65389684c398|2017-12-13 13:20:24.430|
               3|568204da-93d7-42f4-8a7a-4446a144277d|2017-12-13 13:20:24.540|
               4|0eff57b9-4f4f-41a6-8867-658c199a5fc0|2017-12-13 13:20:24.570|
               5|b82f88d1-ff79-4fd9-8c54-9d24c140f647|2017-12-13 13:20:24.633|
               6|1b3d077a-1941-4d6e-8328-f7dc03595565|2017-12-13 13:20:24.680|
               7|c1898370-a36f-43a2-987c-0bf24fe3fb82|2017-12-13 13:20:24.727|
               8|2b50abb8-abab-412b-a4d0-4fd5ebeb5cbe|2017-12-13 13:20:24.773|
               9|5c0ab449-a087-4d8d-834f-3726061b6bfa|2017-12-13 13:20:24.803|
              10|0f3cc1d7-f484-4bde-b088-b11ef03e2f52|2017-12-13 13:20:24.850|
              11|a417a3d1-00eb-4d7f-b793-f93dc2c5391d|2017-12-13 13:20:24.900|
              12|ebd8a50f-322e-4426-a39a-566fd5535b1c|2017-12-13 13:20:24.947|
			  -- more --

Click to view Full table

Sample table: Person.Person
businessentityid|persontype|namestyle|title|firstname               |middlename      |lastname              |suffix|emailpromotion|additionalcontactinfo|demographics|rowguid                             |modifieddate           |
----------------+----------+---------+-----+------------------------+----------------+----------------------+------+--------------+---------------------+------------+------------------------------------+-----------------------+
               1|EM        |false    |     |Ken                     |J               |Sánchez               |      |             0|                     |[XML]       |92c4279f-1207-48a3-8448-4636514eb7e2|2009-01-07 00:00:00.000|
               2|EM        |false    |     |Terri                   |Lee             |Duffy                 |      |             1|                     |[XML]       |d8763459-8aa8-47cc-aff7-c9079af79033|2008-01-24 00:00:00.000|
               3|EM        |false    |     |Roberto                 |                |Tamburello            |      |             0|                     |[XML]       |e1a2555e-0828-434b-a33b-6f38136a37de|2007-11-04 00:00:00.000|
               4|EM        |false    |     |Rob                     |                |Walters               |      |             0|                     |[XML]       |f2d7ce06-38b3-4357-805b-f4b6b71c01ff|2007-11-28 00:00:00.000|
               5|EM        |false    |Ms.  |Gail                    |A               |Erickson              |      |             0|                     |[XML]       |f3a3f6b4-ae3b-430c-a754-9f2231ba6fef|2007-12-30 00:00:00.000|
               6|EM        |false    |Mr.  |Jossef                  |H               |Goldberg              |      |             0|                     |[XML]       |0dea28fd-effe-482a-afd3-b7e8f199d56f|2013-12-16 00:00:00.000|
               7|EM        |false    |     |Dylan                   |A               |Miller                |      |             2|                     |[XML]       |c45e8ab8-01be-4b76-b215-820c8368181a|2009-02-01 00:00:00.000|
               8|EM        |false    |     |Diane                   |L               |Margheim              |      |             0|                     |[XML]       |a948e590-4a56-45a9-bc9a-160a1cc9d990|2008-12-22 00:00:00.000|
               9|EM        |false    |     |Gigi                    |N               |Matthew               |      |             0|                     |[XML]       |5fc28c0e-6d36-4252-9846-05caa0b1f6c5|2009-01-09 00:00:00.000|
			   -- more --

Click to view Full table

Sample Solution:

-- Selecting ProductID and Name columns from the Product table where ProductID is not 3 or 4, and combining the result with the ProductModelID and Name columns from the ProductModel table using the UNION operator
SELECT 
    -- Selecting the ProductID column and Name column from the Product table
    ProductID, 
    Name  
-- Selecting data from the Product table, filtering out ProductID values 3 and 4
FROM 
    Production.Product  
-- Filtering records where ProductID is not 3 or 4
WHERE 
    ProductID NOT IN (3, 4)  
-- Combining the results with the following SELECT statement using the UNION operator
UNION  
-- Selecting the ProductModelID column and Name column from the ProductModel table
SELECT 
    ProductModelID, 
    Name  
-- Selecting data from the ProductModel table
FROM 
    Production.ProductModel   
-- Ordering the result set by Name
ORDER BY 
    Name;

Explanation:

  • This SQL code retrieves ProductID and Name columns from the Product table where ProductID is not 3 or 4, and combines the result with the ProductModelID and Name columns from the ProductModel table using the UNION operator.
  • The first SELECT statement selects ProductID and Name columns from the Product table, filtering out ProductID values 3 and 4.
  • The WHERE clause filters records where ProductID is not 3 or 4.
  • The UNION operator combines the results of the first SELECT statement with the results of the second SELECT statement, ensuring that duplicate rows are removed.
  • The second SELECT statement selects ProductModelID and Name columns from the ProductModel table.
  • The ORDER BY clause orders the combined result set 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.



Become a Patron!

Follow us on Facebook and Twitter for latest update.

It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.

https://www.w3resource.com/sql-exercises/adventureworks/sql-adventureworks-exercise-137.php