CODEWITHSUNDEEP
sqlsql-server-2008-r2
aantiix
aantiix

Reputation: 534

SELECT CASE when DISTINCT

I need to list all cities in a database where there is an active employee. If the city exists in more than one state, I want to append the state name to it. So I'm trying to do something like:

SELECT Locations.LocationID,
    CASE
        WHEN Locations.City IS NOT DISTINCT
            THEN Locations.City + '(' + ISNULL(Locations.State,'') + ')'
        ELSE Locations.City
    END as LocationDescription
FROM Locations
    INNER JOIN Employees on Locations.LocationID = Employees.LocationID
WHERE Employees.Active=1 
ORDER BY LocationDescription

Obviously this isn't correct syntax where I'm using DISTINCT, just not sure how to go about this.

Upvotes: 1

Views: 1143

Answers (2)

jpw
jpw

Reputation: 44891

You could do it using a correlated subquery, but wouldn't it be easier to always append the state? It would make the output more consistent too...

There are probably smarter ways to do it, but this should work.

SELECT l.LocationID,
    CASE
        WHEN (SELECT COUNT(City) FROM Locations WHERE L.city = city) > 1
            THEN l.City + '(' + ISNULL(l.State,'') + ')'
          ELSE l.City
    END as LocationDescription
FROM Locations L
    INNER JOIN Employees on l.LocationID = Employees.LocationID
WHERE Employees.Active=1 
ORDER BY LocationDescription

Upvotes: 2

ragerory
ragerory

Reputation: 1378

I would go with the option to add the state every time, however, if you really want to do it this way you can use GROUP BY and IN clauses...

WHEN Locations.City IN (SELECT Locations.City FROM Location GROUP BY City HAVING COUNT(*) > 1)

Upvotes: 2

Related Questions