January 5, 2006 at 7:00 am
Hi i am havin a bit of trouble with the joins in this query as some of the values stored in the main table may be entered into the table as nulls so
when i try to join the another table using these values it will not work an i dont get back any rows does anyone have eny ideas how to solve this problem
Thanks in advance Tim
select IsNull(p.firstName+ ' ','') + IsNull(p.MiddleName+ ' ','') + IsNull(p.LastName,'') as Name, i.Diagnosis as Injury,
SUBSTRING(CAST(i.Date as nvarchar), 0 ,12) as Date, im2.Name as Prognosis, imgoi.Name as GradOfInjury, impob.Name as PartOfBody,
imtt.Name as TissueType, imact.Name as Activity, imsur.Name as Surface, imsurcon.Name as SurfaceCondidtions, imfw.Name as Footwear,
'Recovered' = CASE WHEN i.Date IS NOT NULL and (i.DateRecovered IS NULL)
THEN 'No' ELSE 'Yes' END from injury i
join Person p on p.Id = i.PersonID
join InjuryMapping im2 on im2.ID = i.Prognosis
join InjuryMapping imgoi on imgoi.ID = i.GradeOfInjury
join InjuryMapping impob on impob.ID = i.PartOfBody
join InjuryMapping imtt on imtt.ID = i.TissueType
join InjuryMapping imact on imact.ID = i.Activity
join InjuryMapping imsur on imsur.ID = i.Surface
join InjuryMapping imsurcon on imsurcon.ID = i.SurfaceConditions
join InjuryMapping imfw on imfw.ID = i.Footwear
where (p.ID = 3 and i.ID = 87) or (p.ID = 7 and i.ID = 82)
order by p.Lastname
January 5, 2006 at 7:10 am
use LEFT OUTER JOIN for any column that maybe null and check for null when ouput (as per p.firstName etc)
Far away is close at hand in the images of elsewhere.
Anon.
January 5, 2006 at 8:43 am
so simple and yet so perfect... nice one thanks alot
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply