November 4, 2007 at 10:09 pm
When joining across multiple tables query result returns more rows than expected.
What's wrong!
November 4, 2007 at 10:13 pm
Hi Nathan,
Here is the SQL that is being a pain and returning multiple rows, despite their being only one row that actually exists in the table.
I thought that by using the left outer join it gave me a recordset even if the left hand-side of the join contained a NULL or non-matching value.
But obviously I have missed something.
Thanks again for you help too.
select
pinf.NHINumber,
t.titledesc,
tm.teamname,
pinf.familyname,
pinf.interpreterRequired,
pinf.firstname,
pinf.secondname,
pinf.thirdname,
pinf.preferredname,
pinf.dob,
eg.ethinicName,
pinf.religion,
pinf.nurse_name,
mt.maritalstatus,
pinf.preferredlanguage,
deg.DegEngDesc,
pinf.primarynurse,
pinf.weightonadmission,
pinf.ageofmother,
pinf.dateofAdmission,
pinf.address1,
pinf.address2,
pinf.city,
pinf.country,
c.country as cob,
inc.incomesource,
ca.carerAvailDesc,
la.livingArr,
pinf.suburb,
pinf.homephone,
pinf.workphone,
pinf.mobilephone,
pinf.email,
pinf.gestationperiod,
pinf.email,
ins.insStatusDesc,
pinf.privateInsurance,
rc.consent,
patientterminalcare,
carerterminalcare,
r.refdate,
pp.principalPurchaser,
d.domname,
g.gendername,
pinf.coordinatorassigned,
pinf.communityservicecard,
pinf.pharmacy,
pinf.telephone,
pinf.fax,
pinf.iwi,
pinf.region_code,
d.domname,
reg.region,
pinf.comments,
pinf.carersupport,
pinf.carersupportcomments,
pinf.community_card_no,
l.location
from
patientinfo pinf
left outer join
title t on pinf.titleid = t.titleid
left outer join
team tm on pinf.teamid = tm.teamid
left outer join
ethinicGroup eg on pinf.ethinicid = eg.ethinicid
left outer join
maritalstatus mt on pinf.maritalstatusid = mt.maritalstatusId
left outer join
degreeOfEnglish deg on pinf.degengid = deg.degengid
left outer join
country c on pinf.countryid = c.countryid
left outer join
insuranceStatus ins on pinf.insstatusid = ins.insstatusid
left outer join
researchConsent rc on pinf.rcid = rc.rcid
left outer join
incomeSource inc on pinf.incomeSourceId = inc.incomeSourceId
left outer join
carerAvailability ca on pinf.carerAvailId = ca.carerAvailId
left outer join
livingArrangement la on pinf.livingArrId = la.livingArrId
left outer join
ref_patientinfo r on pinf.NHINumber=r.NHINumber
left outer join
principalpurchaser pp on pinf.principalPurchasercode=pp.principalPurchasercode
left outer join
domicile d on pinf.domid=d.domid
left outer join
region reg on pinf.region_code=reg.region_code
left outer join
genderlist g on pinf.genderid=g.genderid
left outer join
location l on pinf.locationid=l.locationid
where
patientid = #session.patid#
Gavin Baumanis
Smith and Wesson. The original point and click device.
November 4, 2007 at 10:25 pm
Your query looks good to me...
Get the patient information,
return the title (LEFT JOIN TITLE),
return the teamname (LEFT JOIN TEAM),
There are a lot of joins though which could be over-engineering...
What does the resultset look like exactly?
November 4, 2007 at 10:26 pm
Here is the recordset.
"NHN1234";"";"Nelson-Richmond";"Baumanis";0;"Gavin";"Andrew";"";"Beau";"1920-08-11 00:00:00+10";"European not further defined";"";"";"";"";"";"";"";"";"";"657 Nicholson Street";"";"Melbourne";"";"Australia";"";"";"";"Carlton North";"61-3-93814567";"";"";"";"";"";"";"";"";"";"";"2007-07-12 00:00:00+10";"";"Clarence";"Male";;"";"";"";"";"";;"Clarence";"";"";;"";"";""
Only it returns 9 rows exactly the same.
Gavin Baumanis
Smith and Wesson. The original point and click device.
November 4, 2007 at 10:46 pm
GavinB noticed that the timestamp column was different in easch row and we've now realised it's some history information - so the joins were working properly!
!!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply