Left outer join?

1

Have got a bit of an issue here. See below sql statement, which supposed to bring incident data. I'm interested in highlighted areas. When [AppUserRole_RelatedItem] has no record the output should still bring data from INCIDENT table, but input blank in ReportedBy field. How do I do that?

SELECT [incident].id
,[Site].Name
,CASE WHEN [System_Category_Module].Category_ID = 914 THEN 'First Aid'
WHEN [System_Category_Module].Category_ID = 136 THEN 'Near Hits'
WHEN [System_Category_Module].Category_ID = 1788 THEN 'Breach Incident'
WHEN [System_Category_Module].Category_ID = 1789 THEN 'No-Breach Incident'
WHEN [System_Category_Module].Category_ID = 1787 THEN 'Complaints'
WHEN [System_Category_Module].Category_ID = 1785 THEN 'Regulator Visit'
WHEN [System_Category_Module].Category_ID = 1786 THEN 'Regulator Correspondence'
END As Inc_Type
,[Incident].[Inc_Date]
,[Incident].[Details]
,CASE WHEN [AppUserRole_RelatedItem].UserRole_ID > 0 THEN AppUser.Surname + ', ' + AppUser.Forename
WHEN [AppUserRole_RelatedItem].UserRole_ID < 0 THEN [AppUserRole_RelatedItem].Other_Description
WHEN [AppUserRole_RelatedItem].UserRole_ID = '' THEN 'NA'
END As ReportedBy

FROM [Envoy].[dbo].[Incident]
JOIN [Site] on [Site].ID = [Incident].Site_ID
JOIN [System_Category_Module] on [System_Category_Module].Module_ID = [Incident].ID
JOIN [AppUserRole_RelatedItem] on [Incident].ID = [AppUserRole_RelatedItem].Module_ID
LEFT JOIN [AppUser] on [AppUser].ID = [AppUserRole_RelatedItem].UserRole_ID

WHERE [Incident].Inc_Date >= DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0)

AND ([System_Category_Module].Category_ID = 914
OR [System_Category_Module].Category_ID = 136
OR [System_Category_Module].Category_ID = 1788
OR [System_Category_Module].Category_ID = 1789
OR [System_Category_Module].Category_ID = 1787
OR [System_Category_Module].Category_ID = 1785
OR [System_Category_Module].Category_ID = 1786)

AND [AppUserRole_RelatedItem].Sub_Code = 'REP_BY'

2

LEFT OUTER JOIN [AppUserRole_RelatedItem] on [Incident].ID = [AppUserRole_RelatedItem].Module_ID AND [AppUserRole_RelatedItem].Sub_Code = 'REP_BY'

And remove the 'REP_BY' from the WHERE clause, move it to the LEFT JOIN instead.

3

It doubles every single record.

4

use distinct

5

Distinct won't work either, ntext data type cannot be selected as DISTINCT because it is not comparable.

6

try this

SELECT 
	 [incident].id
	,[Site].Name
	,CASE WHEN [System_Category_Module].Category_ID = 914 THEN 'First Aid'
	 WHEN [System_Category_Module].Category_ID = 136 THEN 'Near Hits'
	 WHEN [System_Category_Module].Category_ID = 1788 THEN 'Breach Incident'
	 WHEN [System_Category_Module].Category_ID = 1789 THEN 'No-Breach Incident'
	 WHEN [System_Category_Module].Category_ID = 1787 THEN 'Complaints'
	 WHEN [System_Category_Module].Category_ID = 1785 THEN 'Regulator Visit'
	 WHEN [System_Category_Module].Category_ID = 1786 THEN 'Regulator Correspondence'
	 END As Inc_Type
	,[Incident].[Inc_Date]
	,[Incident].[Details]
	,CASE WHEN [AppUserRole_RelatedItem].UserRole_ID > 0 THEN AppUser.Surname + ', ' + AppUser.Forename
	WHEN [AppUserRole_RelatedItem].UserRole_ID < 0 THEN [AppUserRole_RelatedItem].Other_Description
	WHEN [AppUserRole_RelatedItem].UserRole_ID = '' THEN 'NA'
	END As ReportedBy
FROM [Envoy].[dbo].[Incident]

JOIN [Site] on [Site].ID = [Incident].Site_ID

JOIN [System_Category_Module] on [System_Category_Module].Module_ID = [Incident].ID

left JOIN (select distinct * from [AppUserRole_RelatedItem] where Sub_Code = 'REP_BY')[AppUserRole_RelatedItem] 
on [Incident].ID = [AppUserRole_RelatedItem].Module_ID 

LEFT JOIN [AppUser] on [AppUser].ID = [AppUserRole_RelatedItem].UserRole_ID
WHERE 
	[Incident].Inc_Date >= DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0)
AND [System_Category_Module].Category_ID in (914,136,1788,1789,1787,1785,1786)
7

Wow, thanks. From the first glance that is looking exactly what I needed it to do. I will be testing it in different situations tomorrow, thus will post more feedback when finish.

Greatly appreciated for help.

8

All works just fine.

Thank you again for your time and afford.