I'm trying to figure out how do a particular query.

Given that I know how to get the following results from a query (details below):

client1, MT
client2, WA
client3, MT
client3, WA
client3, ID
...

How do I fashion a query that, when a client is listed in more than one state (like client3), the query will only return a single record (instead of three) but print 'MULTIPLE' instead of listing the specific states.

<details>
Here's the set up. I have a table of clients (tblClients), a table of US states (tblStates) and 'linking' table (tblClientState) to id which states a particular client operates in. The reason for the linking table is that a client can operate in one or many different states.

The tables look like this:

tblClients:
client_id, client_name

tblStates:
state_abbrv, state_name

tblClientState:
client_id, state_abbrv

An example query I'm working with is to return a list with the client_name and the state_abbrv.

SELECT client_name, state
FROM tblClients JOIN tblClientState ON tblClients.client_id = tblClientState.client_id

This would return a result like this:

client1, MT
client2, WA
client3, MT
client3, WA
client3, ID
...

Again, here's my question:

How do I fashion a query that, when a client is in more than one state (like client3), the query will only return a single record (instead of three) but have it say 'MULTIPLE' instead of listing the specific states.

It seems like I might be able to use some conditions in my SELECT statement but I can't figure out how to make it all happen.
</details>

Thanks!

Eric Lund

Try using CASE:

SELECT client_name,
CASE WHEN COUNT(*) = 1 THEN MAX(state) ELSE 'MULTIPLE' END
FROM ...
GROUP BY client_name;

Wow! Perfect!

That's just the kind of simple, sweet answer I was hoping I would get out of this group!

I was able to take your suggestion and build on it a little bit (because of course, my example was a little over simplified) and I got just EXACTLY what I was looking for.

Thanks a LOT. I really appreciate it!

Eric