From: rrego@pmchnnj.org   
      
   I have this following sql statement with a lot of help from this group.   
      
   I question is I have a child table - toxnotes - which has many entry notes for   
   the parent - toxpat - casenumber.   
      
   Can I go throiught all notes in toxnotes for casenumber = tp.casenumber and   
   bundled them into one display   
      
      
   /****** cv_ct = Codevalue table & field calltype  ******/   
   /****** cv_mo = Codevalue table & field medicaloutcome  ******/   
   /****** cv_cs = Codevalue table & field callersitee  ******/   
   /****** cv_pt = Codevalue table & field patgender  ******/   
   /****** cv_cr = Codevalue table & field caller relation to pat  ******/   
   /****** cv_er = Codevalue table & field exp reason  ******/   
   /****** cv_es = Codevalue table & field exp site  ******/   
   /****** cv_ms = Codevalue table & field management site  ******/   
      
   /****** Alias tp = ToxPat  ******/   
   /****** Alias tes = ToxExpSub  ******/   
   /****** Alias tr = ToxExpRoute  ******/   
   /****** Alias te = ToxExp  ******/   
   /****** Alias hos = UHCF ******/   
      
   use njpies2013   
      
   SELECT tp.CaseNumber, cast(tp.StartDate as date) as "Call Date",   
   ast(tp.StartDate as time(0)) as "Call Time", cv_ct.CodeValue as "Call Type",   
   cv_cs.CodeValue as "Call Site", hos.UHCFName as "Site Loc",cv_pt.CodeValue as   
   "Pat Type",   
   	 tp.PatAge, cv_pa.CodeValue as "Age Unit", cv_cr.CodeValue as "Caller   
   Relation", cast(tp.callerzip as text)as "Zip", tp.CallerState, tp.CallerPhone,   
   tp.PatPregDuration,   
   	 cv_er.CodeValue as "Call Reason", cv_es.CodeValue as "Exp Site",    
   tes.SubDesc,tes.SubPoisindexCode, cv_mo.CodeValue AS "Med Outcome",   
   	 cv_ms.CodeValue AS "Mgmt Site", tr.route_ingestion, tr.Route_Inhalation,   
   tr.Route_Aspiration, tr.Route_Ocular, tr.Route_Dermal,   
   	 tr.Route_Bite, tr.Route_Parenteral, tr.Route_Rectal, tr.Route_Otic,   
   tr.Route_Vaginal, tr.Route_Other, tr.Route_Unknown   
   	   
   from ToxExpSub  tes   
   join ToxExpRoute tr on tr.casenumber = tes.CaseNumber   
   join ToxExp te on te.CaseNumber = tes.CaseNumber   
   join Toxpat tp  on tp.CaseNumber = tes.CaseNumber   
   full join UHCF hos on hos.UHCFNumber = tp.CallerSiteCode   
   full join Codevalue cv_pa on cv_pa.CodeID = tp.patageunit   
   full join CodeValue cv_ct  on cv_ct.CodeID = tp.calltype   
   full join CodeValue cv_mo on cv_mo.CodeID = te.MedicalOutcome   
   full join CodeValue cv_cs on cv_cs.CodeID = tp.CallerSite   
   full join CodeValue cv_pt on cv_pt.CodeID = tp.PatGender   
   full join CodeValue cv_cr on cv_cr.CodeID = tp.CallerRelToPat   
   full join CodeValue cv_er on cv_er.CodeID = tp.ExpReason   
   full join  CodeValue cv_es on cv_es.CodeID = tp.ExpSite   
   full join CodeValue cv_ms on cv_ms.CodeID = te.ManagementSite   
   where exists (   
   select 1   
   from ToxExpSub   
   where CaseNumber =tes.CaseNumber   
   AND SubPoisindexCode = 6931087   
   )   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)