Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #1825

Re: data retrieve on parent and many child instances

Show all headers | View raw

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 

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

data retrieve on parent and many child instances Raul Rego <rrego@pmchnnj.org> - 2014-10-27 22:47 -0700
  Re: data retrieve on parent and many child instances Lennart Jonsson <erik.lennart.jonsson@gmail.com> - 2014-10-28 07:08 +0100
  Re: data retrieve on parent and many child instances Raul Rego <rrego@pmchnnj.org> - 2014-10-28 02:54 -0700
    Re: data retrieve on parent and many child instances Erland Sommarskog <esquel@sommarskog.se> - 2014-10-28 11:37 +0000

csiph-web