SQL server query demonstrating inner and outer joins

Here’s an example of a SQL Server query demonstrating inner and outer joins.


SELECT
q.[field1]
,REPLACE(REPLACE(CONVERT(nvarchar(max),q.[field2]), CHAR(13), ''), CHAR(10), '') AS 'friendly column name'
,(u.field3 + ' ' + u.field4) AS 'Name'
,ld.[field5]
,doc.[field6] AS 'Document ref'
,doc.[field7]
FROM [FreedomOfInformation].[dbo].[Table1] q
INNER JOIN [Database1].[dbo].[Table2] u ON u.[UserID] = q.[UserID]
INNER JOIN [Database1].[dbo].[Table3] l ON l.[LogID] = q.[LogID]
INNER JOIN [Database1].[dbo].[Table4] lt ON lt.[topicId] = q.[TopicID]
LEFT OUTER JOIN [Database1].[dbo].[Table5] ld ON ld.[qaid] = q.[QAID]
LEFT OUTER JOIN [Database2].[dbo].[Table6] doc ON ld.[DocumentID] = doc.[DocumentID]
WHERE q.[DatetimeAdded] BETWEEN DATEADD(YEAR, -3, GETDATE()) AND GETDATE()
ORDER BY q.[DatetimeAdded]

Leave a Reply

Your email address will not be published. Required fields are marked *