Issues trying to select unique results in MySQL sub query

Question

Having to write a select statement that returns the name, city, and state of each vendor that's located in a unique city and state. I've seen several similar threads but none of the answers given are helping for some reason. My code is still unable to return the proper number of results:

Write a select statement that returns the name, city, and state of each vendor that's located in a unique city and state. It needs to return 38 rows.

/*problem 6*/

SELECT 
vendor_name, vendor_city, vendor_state
FROM
vendors
WHERE
vendor_city || vendor_state NOT IN (SELECT distinct
        vendor_city || vendor_state
    FROM
        vendors
    GROUP BY vendor_city, vendor_state having count(*)>1)
ORDER BY vendor_state , vendor_city;

Answer 1

This query should satisfy one possible interpretation of the specification:

  SELECT MIN(v.vendor_name) AS vendor_name
       , v.vendor_city
       , v.vendor_state
    FROM vendors v
   GROUP
      BY v.vendor_city
       , v.vendor_state
  HAVING COUNT(*) = 1

This is getting the (city,state) tuples, and counting the number of rows that have the same values, and excluding any where more than one row has the same values. Of the rows that remain, there will be only one vendor_name to return.

(The spec might also be interpreted differently e.g. the same vendor_name in multiple (city,state), and we are wanting to return the vendor_name that occur only in a single (city,state). That would require a different query.)

---

An extension in MySQL allows us to leave off the aggregate function, if ONLY_FULL_GROUP_BY is not included in sql_mode i.e. we can replace MIN(v.vendor_name) with just v.vendor_name.

If there's some requirement that we must use a nested query, I'd much prefer to use an inline view to return the "unique city and state", and then a join operation.

  SELECT t.vendor_name
       , t.vendor_city
       , t.vendor_state
    FROM ( SELECT v.vendor_city
                , v.vendor_state
             FROM vendors v
            GROUP
               BY v.vendor_city
                , v.vendor_state
           HAVING COUNT(*) = 1
         ) u
    JOIN vendors t
      ON t.vendor_city  <=> u.vendor_city
     AND t.vendor_state <=> u.vendor_state 
   ORDER
      BY t.vendor_name