Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2003
    Posts
    2

    Unanswered: How to do this conditional(maybe?) query

    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

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: How to do this conditional(maybe?) query

    Try using CASE:

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

  3. #3
    Join Date
    Sep 2003
    Posts
    2

    Thumbs up Re: How to do this conditional query

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •