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

adding a column to select

X-Received	by 10.66.145.232 with SMTP id sx8mr17608191pab.18.1414777658564; Fri, 31 Oct 2014 10:47:38 -0700 (PDT)
X-Received	by 10.140.93.106 with SMTP id c97mr527qge.41.1414777658485; Fri, 31 Oct 2014 10:47:38 -0700 (PDT)
Path	csiph.com!v102.xanadu-bbs.net!xanadu-bbs.net!news.glorb.com!r10no2298788igi.0!news-out.google.com!u5ni19qab.1!nntp.google.com!i13no321986qae.0!postnews.google.com!glegroupsg2000goo.googlegroups.com!not-for-mail
Newsgroups	comp.databases.ms-sqlserver
Date	Fri, 31 Oct 2014 10:47:38 -0700 (PDT)
Complaints-To	groups-abuse@google.com
Injection-Info	glegroupsg2000goo.googlegroups.com; posting-host=130.219.148.42; posting-account=WG_AKQoAAABOEUiyIf1Ow04_BDE0XUgq
NNTP-Posting-Host	130.219.148.42
User-Agent	G2/1.0
MIME-Version	1.0
Message-ID	<74f6aa18-3f13-4af6-9664-9560eab46eff@googlegroups.com> (permalink)
Subject	adding a column to select
From	Raul Rego <rrego@pmchnnj.org>
Injection-Date	Fri, 31 Oct 2014 17:47:38 +0000
Content-Type	text/plain; charset=ISO-8859-1
Xref	csiph.com comp.databases.ms-sqlserver:1831

Show key headers only | View raw

I have this following sql statement with a lot of help from this group.

I question is I have a child table - toxnotes - which has many entry notes for the parent - toxpat - casenumber.

Can I go throiught all notes in toxnotes for casenumber = tp.casenumber and bundled them into one display


/****** cv_ct = Codevalue table & field calltype  ******/
/****** cv_mo = Codevalue table & field medicaloutcome  ******/
/****** cv_cs = Codevalue table & field callersitee  ******/
/****** cv_pt = Codevalue table & field patgender  ******/
/****** cv_cr = Codevalue table & field caller relation to pat  ******/
/****** cv_er = Codevalue table & field exp reason  ******/
/****** cv_es = Codevalue table & field exp site  ******/
/****** cv_ms = Codevalue table & field management site  ******/

/****** Alias tp = ToxPat  ******/
/****** Alias tes = ToxExpSub  ******/
/****** Alias tr = ToxExpRoute  ******/
/****** Alias te = ToxExp  ******/
/****** Alias hos = UHCF ******/

use njpies2013

SELECT tp.CaseNumber, cast(tp.StartDate as date) as "Call Date",cast(tp.StartDate as time(0)) as "Call Time", cv_ct.CodeValue as "Call Type", cv_cs.CodeValue as "Call Site", hos.UHCFName as "Site Loc",cv_pt.CodeValue as "Pat Type", 
	 tp.PatAge, cv_pa.CodeValue as "Age Unit", cv_cr.CodeValue as "Caller Relation", cast(tp.callerzip as text)as "Zip", tp.CallerState, tp.CallerPhone, tp.PatPregDuration,
	 cv_er.CodeValue as "Call Reason", cv_es.CodeValue as "Exp Site",  tes.SubDesc,tes.SubPoisindexCode, cv_mo.CodeValue AS "Med Outcome", 
	 cv_ms.CodeValue AS "Mgmt Site", tr.route_ingestion, tr.Route_Inhalation, tr.Route_Aspiration, tr.Route_Ocular, tr.Route_Dermal,
	 tr.Route_Bite, tr.Route_Parenteral, tr.Route_Rectal, tr.Route_Otic, tr.Route_Vaginal, tr.Route_Other, tr.Route_Unknown
	
from ToxExpSub  tes 
join ToxExpRoute tr on tr.casenumber = tes.CaseNumber
join ToxExp te on te.CaseNumber = tes.CaseNumber
join Toxpat tp  on tp.CaseNumber = tes.CaseNumber 
full join UHCF hos on hos.UHCFNumber = tp.CallerSiteCode
full join Codevalue cv_pa on cv_pa.CodeID = tp.patageunit
full join CodeValue cv_ct  on cv_ct.CodeID = tp.calltype
full join CodeValue cv_mo on cv_mo.CodeID = te.MedicalOutcome
full join CodeValue cv_cs on cv_cs.CodeID = tp.CallerSite
full join CodeValue cv_pt on cv_pt.CodeID = tp.PatGender
full join CodeValue cv_cr on cv_cr.CodeID = tp.CallerRelToPat
full join CodeValue cv_er on cv_er.CodeID = tp.ExpReason
full join  CodeValue cv_es on cv_es.CodeID = tp.ExpSite
full join CodeValue cv_ms on cv_ms.CodeID = te.ManagementSite
where exists (
select 1
from ToxExpSub
where CaseNumber =tes.CaseNumber
AND SubPoisindexCode = 6931087
)

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

Thread

adding a column to select Raul Rego <rrego@pmchnnj.org> - 2014-10-31 10:47 -0700
  Re: adding a column to select Erland Sommarskog <esquel@sommarskog.se> - 2014-11-01 15:06 +0100
    Re: adding a column to select Raul Rego <rrego@pmchnnj.org> - 2014-11-03 05:13 -0800
      Re: adding a column to select Erland Sommarskog <esquel@sommarskog.se> - 2014-11-03 18:14 +0100
        Re: adding a column to select Ross Presser <rpresser@gmail.com> - 2014-11-03 09:56 -0800

csiph-web