Quick query question

  • Goalie35

    SSC Eights!

    Points: 854

    I need to write a query that joins 2 tables.

    Table 1(Clients) contains the clientname and the clientid

    Table2(ClientContacts) contains data such as who is the administrator and business agent for each client is. 

    Now, here's the tricky part.  Some clients have multiple adminstrators assigned to them, meaning my "ClientContacts" table will have multiple rows of data for certain clients, however, no matter how many administrators a client may have assigned to them, there should always be one adminstrator in the group that is listed as the head administrator(we call them the Primary Admin.).

    Ok, so I need to create a query that finds all clients who may or may not have administrators BUT none of these administrators are listed as the primary administrator. 

    So here's a sample query that I tried but obviously this is wrong because this finds simply finds all rows where the admin is not the primary agent:

    ------------------------------------------------------------------

    select clientid, clientname, clientcontprimaryagtind from tclients

    inner join tclientcontacts on clientcontclientid = clientid

    where clientcontprimaryagtind <> 'Y'

    -----------------------------------------------------------------

    Any idea how to fix this to extract the data I need?

    Thanks in advance.

     

    -Goalie35

     

  • PW-201837

    SSC-Insane

    Points: 20805

    select clientid, clientname, clientcontprimaryagtind

    from tclients

    Where Not Exists (

      Select *

      From tclientcontacts

      Where clientcontclientid = clientid

      And   clientcontprimaryagtind = 'Y'

    )

  • Goalie35

    SSC Eights!

    Points: 854

    Thanks PW

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply