From: erik.lennart.jonsson@gmail.com   
      
   On 2014-10-28 06:47, Raul Rego wrote:   
   > I have the following Select:   
   > SELECT   
   >         ToxPat.CaseNumber, ToxPat.StartDate, ToxExpSub.SubDesc,   
   ToxExpSub.SubPoisindexCode, CodeValue.CodeValue as 'Call Type'   
   > from ToxExpSub, Toxpat   
   > inner join CodeValue   
   >   
   > on (codevalue.CodeID = toxpat.calltype)   
   > where ToxExpSub.SubPoisindexCode = 6931087 and ToxPat.CaseNumber =   
   ToxExpSub.CaseNumber   
   >   
   >   
   > This works good but only gets all matches for Poisendexcode=6931087   
   > My problem is that there could be many records in child table ToxExpSub for   
   casenumber = toxpat.casenumber; therefore, there could be many child records   
   even only one matches poisendexcode=6931087.   
   >   
   > I will like all records on table ToxExpsub for canumber = Toxpat.casenumber   
   if one of them has poisendexcode = 6931087   
   >   
      
   First, try not to mix join styles. Let's rewrite using ANSI join's:   
      
   SELECT ToxPat.CaseNumber, ToxPat.StartDate, ToxExpSub.SubDesc   
         , ToxExpSub.SubPoisindexCode, CodeValue.CodeValue as 'Call Type'   
   from ToxExpSub   
   join Toxpat   
        on ToxPat.CaseNumber = ToxExpSub.CaseNumber   
   join CodeValue   
        on codevalue.CodeID = toxpat.calltype   
   where ToxExpSub.SubPoisindexCode = 6931087   
      
      
   Second, you probably need to add an exists predicate (not sure I fully   
   understood your requirements though)   
      
   SELECT ToxPat.CaseNumber, ToxPat.StartDate, ToxExpSub.SubDesc   
         , ToxExpSub.SubPoisindexCode, CodeValue.CodeValue as 'Call Type'   
   from ToxExpSub   
   join Toxpat   
        on ToxPat.CaseNumber = ToxExpSub.CaseNumber   
   join CodeValue   
        on codevalue.CodeID = toxpat.calltype   
   where exists (   
        select 1 from ToxExpSub x   
        where x.SubPoisindexCode = 6931087   
          and x.CaseNumber = ToxPat.CaseNumber   
   )   
      
      
   /Lennart   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)