Path: csiph.com!v102.xanadu-bbs.net!xanadu-bbs.net!feeder.erje.net!eu.feeder.erje.net!news.swapon.de!eternal-september.org!feeder.eternal-september.org!mx02.eternal-september.org!.POSTED!not-for-mail
From: Lennart Jonsson <erik.lennart.jonsson@gmail.com>
Newsgroups: comp.databases.ms-sqlserver
Subject: Re: data retrieve on parent and many child instances
Date: Tue, 28 Oct 2014 07:08:07 +0100
Organization: A noiseless patient Spider
Lines: 48
Message-ID: <m2nbs1$1vf$1@dont-email.me>
References: <67650eda-707d-45f5-9007-11852cc7fe86@googlegroups.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 7bit
Injection-Date: Tue, 28 Oct 2014 06:08:01 +0000 (UTC)
Injection-Info: mx02.eternal-september.org; posting-host="5f53882e1b0b5de864e88de647f19c93"; logging-data="2031"; mail-complaints-to="abuse@eternal-september.org";	posting-account="U2FsdGVkX19vYgCPMPW6YQ2FDxRhhldQ"
User-Agent: Mozilla/5.0 (X11; Linux i686; rv:31.0) Gecko/20100101 Thunderbird/31.2.0
In-Reply-To: <67650eda-707d-45f5-9007-11852cc7fe86@googlegroups.com>
Cancel-Lock: sha1:OVOG4r7g1888eFUtKZazfpVTngg=
Xref: csiph.com comp.databases.ms-sqlserver:1824

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