AdventureWorks Database: A distinct businessentityid must be returned by both queries

Last update on December 20 2024 11:56:55 (UTC/GMT +8 hours)

135. From the following tables write a query in SQL to fetch distinct businessentityid that are returned by both the specified query. Sort the result set by ascending order on businessentityid.

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

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 the common businessentityid values between the BusinessEntity and Person tables filtered by persontype using the INTERSECT operator
SELECT 
    -- Selecting the businessentityid column from the BusinessEntity table
    businessentityid   
-- Selecting data from the BusinessEntity table
FROM 
    person.businessentity    
-- Applying the INTERSECT operator to find the common businessentityid values between the two queries
INTERSECT  
-- Selecting the businessentityid column from the Person table where persontype is 'IN'
SELECT 
    businessentityid   
-- Selecting data from the Person table, filtering by persontype 'IN'
FROM 
    person.person
WHERE 
    person.persontype = 'IN'  
-- Ordering the result set by businessentityid
ORDER BY 
    businessentityid;

Explanation:

• This SQL code retrieves common businessentityid values between the BusinessEntity and Person tables filtered by persontype 'IN' using the INTERSECT operator.
• The first SELECT statement selects businessentityid values from the BusinessEntity table.
• The second SELECT statement selects businessentityid values from the Person table, filtering by persontype 'IN'.
• The INTERSECT operator combines the results of the two SELECT statements and returns only the common businessentityid values present in both tables.
• The result set will contain businessentityid values that exist in both the BusinessEntity and Person tables and are associated with a person of type 'IN'.
• The result set is ordered by businessentityid.

Sample Output:

businessentityid|
----------------+
            1699|
            1700|
            1701|
            1702|
            1703|
            1704|
            1705|
            1706|
            1707|
            1708|
            1709|
            1710|
            1711|
...	

SQL AdventureWorks Editor:

Practice Online

Contribute your code and comments through Disqus.

Previous: Fetch any distinct values from left query of EXCEPT that aren't present in the query to the right.
Next: Any distinct businessentityid from the first query not found in the second query.