SELECT C.ContactID, C.Deleted, M.Lapsed, M.MainSubscription, M.[Current], K.CategoryType
	,A.CompanyName, A.AddressType, A.Address1, A.Address2, A.Address3, A.Town, A.County, A.PostCode, A.Country, A.Telephone1, A.Telephone2, A.Fax
	,C.MobileTelephone, C.EMail, C.WebSite, Categories.CategoryName, C.Title, C.Forenames, C.Surname, C.DateOfBirth
	,A2.CompanyName, A2.Address1, A2.Address2, A2.Address3, A2.Town, A2.County, A2.PostCode, A2.Country, A2.Telephone1, A2.Telephone2, A2.Fax, A2.AddressEmail
	,C.Position, A.Department, A2.AddressType, A2.Position, A2.Department
FROM Contacts C
	JOIN SubscriptionMembers M
		ON C.ContactID = M.ContactID
	JOIN Categories K
		ON C.CategoryCode = K.CategoryCode
	LEFT JOIN Addresses A
		ON C.ContactID = A.ContactID
			-- If these conditions are in the WHERE then the LEFT JOIN will be converted to an INNER JOIN
			AND A.AddressType = N'MAIN'
			AND A.Country = N'United Kingdom'
	LEFT JOIN EnterpriseMRM.dbo.Addresses A2
		ON C.ContactID = A2.ContactID
			-- If this condition is in the WHERE then thn the LEFT JOIN will be converted to an INNER JOIN
			AND A2.AddressType=N'ALTERNATE'
WHERE S.[Current] = 1
	AND K.CategoryType = N'M'
	AND M.MainSubscription = 1
	AND M.Lapsed = 0
	AND C.Deleted=0
ORDER BY C.ContactID, K.CategoryName