This after a few beers, so hopefully this makes some sense ;p

I have an instance where the customer wants to be able to filter on a one->many where if one of the 'many' exists in the
result list, then the client will not be displayed at all.

C.ClientID
C.DisplayName

R.RolodexID
R.Description

CR.ClientID
CR.RolodexID

The following displays all clients of ISRep = True that do not have an entry of (83 - HARTMAN REP), ok.

But if there is a CR entry for another RolodexID value, then the client is displayed.

Is there a way to not display the client at all if HARTMAN REP is present in 'CR', even though there are other
CR.RolodexID entries
that would allow for the client to be selected?

TIA!

Regards,
Scott.

SELECT DISTINCT C.DisplayName, C.ClientID
FROM
    CLIENTS C LEFT OUTER JOIN  ClientRolodex CR ON (C.ClientID=CR.ClientID)
    INNER JOIN  Rolodex R ON (CR.RolodexID=R.RolodexID)
WHERE
(
C.IsRep = TRUE AND
UPPER(R.Description) <> 'HARTMAN REP'
)
ORDER BY 2 ASC

Scott,

<< Is there a way to not display the client at all if HARTMAN REP is present
in 'CR', even though there are other CR.RolodexID entries
that would allow for the client to be selected? >>

Perhaps what you want is something like this ?

SELECT DISTINCT C.DisplayName, C.ClientID
FROM
    CLIENTS C LEFT OUTER JOIN  ClientRolodex CR ON (C.ClientID=CR.ClientID)
    INNER JOIN  Rolodex R ON (CR.RolodexID=R.RolodexID)
WHERE
(
C.IsRep = TRUE AND
UPPER(R.Description) <> 'HARTMAN REP'
)
EXCEPT
SELECT DISTINCT C.DisplayName, C.ClientID
FROM
    CLIENTS C LEFT OUTER JOIN  ClientRolodex CR ON (C.ClientID=CR.ClientID)
    INNER JOIN  Rolodex R ON (CR.RolodexID=R.RolodexID)
WHERE
(
C.IsRep = TRUE AND
UPPER(R.Description)='HARTMAN REP'
)
ORDER BY 2 ASC

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

Makes complete sense... thank you very much!
I really need to spend more time with SQL syntax

Coming from a DBF only background, I never really paid much attention to SQL.

Now that I use it more and more on a daily basis, I wonder what I was thinking in not using it.

Regards,
Scott.

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:C0853E3C-38E9-4F1A-8166-CE1C5B56349A@news.elevatesoft.com...
> Scott,
>
> << Is there a way to not display the client at all if HARTMAN REP is present in 'CR', even though there are other
> CR.RolodexID entries
> that would allow for the client to be selected? >>
>
> Perhaps what you want is something like this ?

Scott


Do you really have a field called 2?


Roy Lambert

Roy,

All my table fields are numbered 1-20, keeps it simple ;P

Actually,

I believe SimpleQuery does this from the Result Field Editor as the 2nd field is the sort field.
Perhaps there is a setting to make it display the actual field name instead of the field index.

Regards,
Scott.

"Roy Lambert" <roy.lambert@skynet.co.uk> wrote in message
news:38DC5866-FA43-463A-9DF3-4B316D1B43FB@news.elevatesoft.com...
> Scott
>
>
> Do you really have a field called 2?
>
>
> Roy Lambert
>

Scott


>All my table fields are numbered 1-20, keeps it simple ;P


Oh boy - are you going to be in trouble with ElevateDB where Tim is surfacing RowID - you'll have to develop a bit of software to go through all of your tables and add 1 to each field <vbcg>

Roy Lambert