Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 6 of 6 total |
Another SQL question... |
Wed, Feb 1 2006 11:48 PM | Permanent Link |
"Scott Martin" | 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 |
Thu, Feb 2 2006 8:30 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@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 ? 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 |
Thu, Feb 2 2006 10:02 AM | Permanent Link |
"Scott Martin" | 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 ? |
Thu, Feb 2 2006 10:18 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Scott
Do you really have a field called 2? Roy Lambert |
Thu, Feb 2 2006 10:34 AM | Permanent Link |
"Scott Martin" | 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 > |
Thu, Feb 2 2006 11:29 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
This web page was last updated on Saturday, May 4, 2024 at 12:54 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |