February 14, 2005 at 10:21 am
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
February 14, 2005 at 10:36 am
select clientid, clientname, clientcontprimaryagtind
from tclients
Where Not Exists (
Select *
From tclientcontacts
Where clientcontclientid = clientid
And clientcontprimaryagtind = 'Y'
)
February 14, 2005 at 10:43 am
Thanks PW
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply