X-Received: by 10.42.192.131 with SMTP id dq3mr2265422icb.3.1414490098295; Tue, 28 Oct 2014 02:54:58 -0700 (PDT)
X-Received: by 10.140.20.108 with SMTP id 99mr16617qgi.15.1414490098150; Tue, 28 Oct 2014 02:54:58 -0700 (PDT)
Path: csiph.com!v102.xanadu-bbs.net!xanadu-bbs.net!news.glorb.com!h15no675602igd.0!news-out.google.com!u5ni10qab.1!nntp.google.com!s7no1099117qap.0!postnews.google.com!glegroupsg2000goo.googlegroups.com!not-for-mail
Newsgroups: comp.databases.ms-sqlserver
Date: Tue, 28 Oct 2014 02:54:57 -0700 (PDT)
In-Reply-To: <67650eda-707d-45f5-9007-11852cc7fe86@googlegroups.com>
Complaints-To: groups-abuse@google.com
Injection-Info: glegroupsg2000goo.googlegroups.com; posting-host=96.242.58.190; posting-account=WG_AKQoAAABOEUiyIf1Ow04_BDE0XUgq
NNTP-Posting-Host: 96.242.58.190
References: <67650eda-707d-45f5-9007-11852cc7fe86@googlegroups.com>
User-Agent: G2/1.0
MIME-Version: 1.0
Message-ID: <f05aee59-7abf-40cc-9d82-90605ce8698f@googlegroups.com>
Subject: Re: data retrieve on parent and many child instances
From: Raul Rego <rrego@pmchnnj.org>
Injection-Date: Tue, 28 Oct 2014 09:54:58 +0000
Content-Type: text/plain; charset=ISO-8859-1
Xref: csiph.com comp.databases.ms-sqlserver:1825

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