AdventureWorks Database: Find stores whose name is the same name as a vendor

SQL Query - AdventureWorks: Exercise-147 with Solution

147. From the following tables write a query in SQL to find stores whose name is the same name as a vendor.

businessentityid|name                                     |salespersonid|demographics|rowguid                             |modifieddate           |
----------------+-----------------------------------------+-------------+------------+------------------------------------+-----------------------+
             292|Next-Door Bike Store                     |          279|[XML]       |a22517e3-848d-4ebe-b9d9-7437f3432304|2014-09-12 11:15:07.497|
             294|Professional Sales and Service           |          276|[XML]       |b50ca50b-c601-4a13-b07e-2c63862d71b4|2014-09-12 11:15:07.497|
             296|Riders Company                           |          277|[XML]       |337c3688-1339-4e1a-a08a-b54b23566e49|2014-09-12 11:15:07.497|
             298|The Bike Mechanics                       |          275|[XML]       |7894f278-f0c8-4d16-bd75-213fdbf13023|2014-09-12 11:15:07.497|
             300|Nationwide Supply                        |          286|[XML]       |c3fc9705-a8c4-4f3a-9550-eb2fa4b7b64d|2014-09-12 11:15:07.497|
             302|Area Bike Accessories                    |          281|[XML]       |368be6dd-30e5-49bb-9a86-71fd49c58f4e|2014-09-12 11:15:07.497|
             304|Bicycle Accessories and Kits             |          283|[XML]       |35f40636-5105-49d5-869e-27e231189150|2014-09-12 11:15:07.497|
             306|Clamps & Brackets Co.                    |          275|[XML]       |64d06bfc-d060-405c-8c60-c067fe7c67df|2014-09-12 11:15:07.497|
             308|Valley Bicycle Specialists               |          277|[XML]       |59386b0c-652e-4668-b44b-4e1711793330|2014-09-12 11:15:07.497|
             310|New Bikes Company                        |          279|[XML]       |47e4b6bd-5cd1-45a3-a231-79d930381c56|2014-09-12 11:15:07.497|
             312|Vinyl and Plastic Goods Corporation      |          282|[XML]       |dc610525-e373-49b1-b786-ea040ec25c06|2014-09-12 11:15:07.497|
             314|Top of the Line Bikes                    |          288|[XML]       |e290e93f-a980-4ba3-86c3-9858f15c8a6d|2014-09-12 11:15:07.497|
-- more --

Click to view Full table

businessentityid|accountnumber|name                             |creditrating|preferredvendorstatus|activeflag|purchasingwebserviceurl    |modifieddate           |
----------------+-------------+---------------------------------+------------+---------------------+----------+---------------------------+-----------------------+
            1492|AUSTRALI0001 |Australia Bike Retailer          |           1|true                 |true      |                           |2011-12-23 00:00:00.000|
            1494|ALLENSON0001 |Allenson Cycles                  |           2|true                 |true      |                           |2011-04-25 00:00:00.000|
            1496|ADVANCED0001 |Advanced Bicycles                |           1|true                 |true      |                           |2011-04-25 00:00:00.000|
            1498|TRIKES0001   |Trikes, Inc.                     |           2|true                 |true      |                           |2012-02-03 00:00:00.000|
            1500|MORGANB0001  |Morgan Bike Accessories          |           1|true                 |true      |                           |2012-02-02 00:00:00.000|
            1502|CYCLING0001  |Cycling Master                   |           1|true                 |true      |                           |2011-12-24 00:00:00.000|
            1504|CHICAGO0002  |Chicago Rent-All                 |           2|true                 |true      |                           |2011-12-24 00:00:00.000|
            1506|GREENWOO0001 |Greenwood Athletic Company       |           1|true                 |true      |                           |2012-01-25 00:00:00.000|
            1508|COMPETE0001  |Compete Enterprises, Inc         |           1|true                 |true      |                           |2011-12-24 00:00:00.000|
            1510|INTERNAT0001 |International                    |           1|true                 |true      |                           |2012-01-25 00:00:00.000|
            1512|LIGHTSP0001  |Light Speed                      |           1|true                 |true      |                           |2011-12-23 00:00:00.000|
            1514|TRAINING0001 |Training Systems                 |           1|true                 |true      |                           |2012-02-03 00:00:00.000|
			-- more --

Click to view Full table

Sample Solution:

-- Selecting distinct store names from the Store table where at least one vendor exists with the same name
SELECT DISTINCT 
    -- Selecting distinct store names from the Name column of the Store table
    s.Name  
-- Selecting data from the Store table with an alias 's'
FROM 
    Sales.Store AS s   
-- Checking for existence of records in a subquery
WHERE 
    EXISTS  
    (
        -- Selecting all records from the Vendor table where the store name matches
        SELECT *  
        FROM 
            Purchasing.Vendor AS v  
        WHERE 
            s.Name = v.Name
    ) ;

Explanation:

• This SQL code retrieves distinct store names from the Store table where at least one vendor exists with the same name.
• The SELECT statement specifies the column to be included in the result set, and DISTINCT ensures only unique store names are returned.
• The WHERE clause includes a subquery using EXISTS to check for the existence of records in the Vendor table where the store name matches.
• The subquery selects all records from the Vendor table where the store name matches with the outer query's store name.
• The outer query selects distinct store names from the Store table where the EXISTS condition is true, i.e., where the subquery returns at least one row.
• The result set will contain distinct store names from the Store table where at least one vendor exists with the same name.

OR

Sample Solution:

-- Selects distinct names from the 'Sales.Store' table
SELECT DISTINCT s.Name  
-- Specifies the 'Sales.Store' table as the source of data and aliases it as 's'
FROM Sales.Store AS s   
-- Filters the result set to include only rows where the store name matches any name in the subquery's result set
WHERE s.Name = ANY  
    -- Subquery: Selects names from the 'Purchasing.Vendor' table
    (SELECT v.Name  
    -- Specifies the 'Purchasing.Vendor' table as the source of data and aliases it as 'v'
    FROM Purchasing.Vendor AS v ) ;	

Explanation:

• The outer SELECT statement retrieves distinct names from the 'Sales.Store' table.
• The FROM clause specifies the source of the data, which is the 'Sales.Store' table, and it is aliased as 's'.
• The WHERE clause filters the result set. It uses the = ANY operator to filter the rows where the store name matches any name in the subquery's result set.
• The subquery retrieves names from the 'Purchasing.Vendor' table, which is aliased as 'v'.

Sample Output:

name|
----+

SQL AdventureWorks Editor:

Practice Online

Contribute your code and comments through Disqus.

Previous: Get employees with Johnson last names.
Next: Find employees of departments that start with P.