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

Re: data retrieve on parent and many child instances

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	Erland Sommarskog <esquel@sommarskog.se>
Newsgroups	comp.databases.ms-sqlserver
Subject	Re: data retrieve on parent and many child instances
Date	Tue, 28 Oct 2014 11:37:13 +0000 (UTC)
Organization	Erland Sommarskog
Lines	35
Message-ID	<XnsA3D48065F21FCYazorman@127.0.0.1> (permalink)
References	<67650eda-707d-45f5-9007-11852cc7fe86@googlegroups.com> <f05aee59-7abf-40cc-9d82-90605ce8698f@googlegroups.com>
Mime-Version	1.0
Content-Type	text/plain; charset=us-ascii
Content-Transfer-Encoding	7bit
Injection-Date	Tue, 28 Oct 2014 11:37:13 +0000 (UTC)
Injection-Info	mx02.eternal-september.org; posting-host="5d404a5d864426cbf50ddbcd9433a6e4"; logging-data="30435"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX18fq1UeryedV0JVyp58cM+k"
User-Agent	Xnews/2005.10.03 Mime-proxy/1.4.c.4 (Win32)
Cancel-Lock	sha1:CzWvTWpnLslqsDbwzFh2nKhsLlo=
Xref	csiph.com comp.databases.ms-sqlserver:1829

Show key headers only | View raw

Raul Rego (rrego@pmchnnj.org) writes:
> 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 


Once you start to learn to use aliass (which you should because it enhances
reaability), it all comes naturally:

SELECT tp.CaseNumber, tp.StartDate, tes.SubDesc, 
      tes.SubPoisindexCode, cv_ct.CodeValue as "Call Type",
      cv_rc.CodeValue AS "Reason to Call"
from ToxExpSub  tes
join Toxpat tp  on tp.CaseNumber = tes.CaseNumber 
join CodeValue  on cv_ct.CodeID = tp.calltype 
join CodeValue  on cv_rc.CodeID = tp.ReasonToCall
where exists ( 
     select 1 from ToxExpSub x 
     where x.SubPoisindexCode = 6931087 
       and x.CaseNumber = tp.CaseNumber 
) 


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Back to comp.databases.ms-sqlserver | Previous | Next — Previous 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