TSQL request is required

The task sounds like this:
Choose the top 10 cities for the next store opening

Columns: City | A priority

Priority is defined as the number of buyers in the city
There should be no shop in the city.

Preliminary wrong decision:

SELECT
    City,
    COUNT(1) as [Priority]
FROM
    Sales.vIndividualCustomer
GROUP BY City
EXCEPT
SELECT
    City,
    COUNT(1) as [Priority]
FROM
    Purchasing.vVendorWithAddresses 
GROUP BY City
ORDER BY [Priority] DESC
GO 

Result:
Uniqueness disappears as soon as I start counting priority. Maybe there is another way?

PS: Used as a database AdventureWorks2016 from Microsoft.

>Solution :

You can use a NOT EXISTS subquery

SELECT
    ic.City,
    COUNT(1) as [Priority]
FROM
    Sales.vIndividualCustomer ic
GROUP BY
    ic.City
HAVING NOT EXISTS (SELECT 1
    FROM
        Purchasing.vVendorWithAddresses va
    WHERE
        va.City = ic.City)
ORDER BY [Priority] DESC;

Note that if you wish to refer to aggregated columns then the NOT EXISTS will have to be in a WHERE not a HAVING

Leave a Reply