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 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: References: <67650eda-707d-45f5-9007-11852cc7fe86@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 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