AdventureWorks Database: Find a match between salesorderheadersalesreason and SalesReason table

SQL Query - AdventureWorks: Exercise-153 with Solution

153. From the following tables write a query in SQL to identify salesorderheadersalesreason and SalesReason tables with the same salesreasonid.

salesorderid|salesreasonid|modifieddate           |
------------+-------------+-----------------------+
       43697|            5|2011-05-31 00:00:00.000|
       43697|            9|2011-05-31 00:00:00.000|
       43702|            5|2011-06-01 00:00:00.000|
       43702|            9|2011-06-01 00:00:00.000|
       43703|            5|2011-06-01 00:00:00.000|
       43703|            9|2011-06-01 00:00:00.000|
       43706|            5|2011-06-02 00:00:00.000|
       43706|            9|2011-06-02 00:00:00.000|
       43707|            5|2011-06-02 00:00:00.000|
       43707|            9|2011-06-02 00:00:00.000|
       43709|            5|2011-06-02 00:00:00.000|
       43709|            9|2011-06-02 00:00:00.000|
	   -- more --

Click to view Full table

salesreasonid|name                     |reasontype|modifieddate           |
-------------+-------------------------+----------+-----------------------+
            1|Price                    |Other     |2008-04-30 00:00:00.000|
            2|On Promotion             |Promotion |2008-04-30 00:00:00.000|
            3|Magazine Advertisement   |Marketing |2008-04-30 00:00:00.000|
            4|Television  Advertisement|Marketing |2008-04-30 00:00:00.000|
            5|Manufacturer             |Other     |2008-04-30 00:00:00.000|
            6|Review                   |Other     |2008-04-30 00:00:00.000|
            7|Demo Event               |Marketing |2008-04-30 00:00:00.000|
            8|Sponsorship              |Marketing |2008-04-30 00:00:00.000|
            9|Quality                  |Other     |2008-04-30 00:00:00.000|
           10|Other                    |Other     |2008-04-30 00:00:00.000|

Click to view Full table

Sample Solution:

-- Selecting all columns from the salesorderheadersalesreason table where salesreasonid matches those in the SalesReason table
SELECT * 
-- From the salesorderheadersalesreason table
FROM sales.salesorderheadersalesreason  
-- Filtering records to include only those where salesreasonid is found in a subquery
WHERE salesreasonid   
IN 
-- Subquery: Selecting salesreasonid from the SalesReason table
(
    SELECT salesreasonid  
    -- From the SalesReason table
    FROM sales.SalesReason
);

Explanation:

• This SQL code retrieves all records from the salesorderheadersalesreason table where the salesreasonid matches those found in the SalesReason table.
• The SELECT statement specifies that all columns should be included in the result set.
• The FROM clause indicates the table from which data is being retrieved, which is the salesorderheadersalesreason table.
• The WHERE clause filters records to include only those where the salesreasonid exists in the subquery result.
• The subquery selects salesreasonid from the SalesReason table.

Sample Output:

salesorderid|salesreasonid|modifieddate           |
------------+-------------+-----------------------+
       43697|            5|2011-05-31 00:00:00.000|
       43697|            9|2011-05-31 00:00:00.000|
       43702|            5|2011-06-01 00:00:00.000|
       43702|            9|2011-06-01 00:00:00.000|
       43703|            5|2011-06-01 00:00:00.000|
       43703|            9|2011-06-01 00:00:00.000|
       43706|            5|2011-06-02 00:00:00.000|
       43706|            9|2011-06-02 00:00:00.000|
       43707|            5|2011-06-02 00:00:00.000|
       43707|            9|2011-06-02 00:00:00.000|
       43709|            5|2011-06-02 00:00:00.000|
       43709|            9|2011-06-02 00:00:00.000|
       43710|            5|2011-06-02 00:00:00.000|
       43710|            9|2011-06-02 00:00:00.000|
	   
...	 

SQL AdventureWorks Editor:

Practice Online

Contribute your code and comments through Disqus.

Previous: Find the salespeople without a quota over $250,000.
Next: Find all telephone numbers that have area code 415.