From: rrego@pmchnnj.org   
      
   On Tuesday, October 28, 2014 1:47:46 AM UTC-4, 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   
      
      
      
   thanks Lamar, this worked   
   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   
   )   
      
   NOW can I issue multiple JOIN like this join CodeValue   
        on codevalue.CodeID = toxpat.calltype .  reason being is Codevalue table   
   has the definitions for many fields numeric entries   
   For example, I will also like to use:   
   Join Codevalue on Codevalue.CodeID = toxpat.ReasonCall   
      
   is it possible to issue multiple Join codevalue on codevalue.codeId =   
   toxpat.Column1, column2, etc   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)