I am wanting to Union the two Select statements below but I want the second statement to only bring back Addresses that are not in the result of the first select statement.
I tried a AddressCode <> Addresscode in the second where clause but it's throwing an error. What am I doing wrong? Any help is appreciated. Thanks.
Select Distinct
PA.AddressCode As Location_CommonName, OT.OccupancyType As Location_OccupancyClassification,InspectionDate As Location_LastSurveyDate,
NextInspectionDate As Location_NextSurveyDate, Recurrance As Location_SurveyFrequency,
(SELECT Distinct InspectionType
FROM dbo.InspectionTypes AS IT_1
WHERE (InspectionType = v_Activities.InspectionType)) As Location_ServiceType,
IT.InspectionType As Location_ServiceType, PA.FullAddress as Location_Address1, PA.SubAddress as Location_Adress2, PA.City As Location_City,
PA.StateAbbr AS Location_State, PA.Zip As Location_Zip, PA.PartyName As Contact_Name, PA.Email As Contact_Email, PA.Phone As Contact_DaytimePhone,
PA.Cell As Contact_MobilePhone , PA.RoleType
from v_PartyAddresses AS PA LEFT OUTER JOIN
dbo.Addresses As Addr on PA.AddressId = Addr.Addressid LEFT OUTER JOIN
dbo.OccupancyTypes As OT on Addr.OccupancyTypeId = OT.OccupancyTypeId Right OUTER JOIN
dbo.v_Activities on Addr.AddressId = v_Activities.AddressId LEFT OUTER JOIN
dbo.InspectionTypes As IT on v_Activities.InspectionTypeId = IT.InspectionTypeId
Where PA.RoleType = 'Property Contact' and PA.AddInact = 'False' and APInactive = 'False' And v_Activities.InspectionCauseId <> '4611aa3c-17c2-4421-81bb-3e973786f26d'
UNION
Select Distinct
PA2.AddressCode As Location_CommonName, OT2.OccupancyType As Location_OccupancyClassification,InspectionDate As Location_LastSurveyDate,
NextInspectionDate As Location_NextSurveyDate, Recurrance As Location_SurveyFrequency,
(SELECT Distinct InspectionType
FROM dbo.InspectionTypes AS IT_1
WHERE (InspectionType = VA2.InspectionType)) As Location_ServiceType,
IT2.InspectionType As Location_ServiceType, PA2.FullAddress as Location_Address1, PA2.SubAddress as Location_Adress2, PA2.City As Location_City,
PA2.StateAbbr AS Location_State, PA2.Zip As Location_Zip, PA2.PartyName As Contact_Name, PA2.Email As Contact_Email, PA2.Phone As Contact_DaytimePhone,
PA2.Cell As Contact_MobilePhone , PA2.RoleType
from v_PartyAddresses AS PA2 LEFT OUTER JOIN
dbo.Addresses As Addr2 on PA2.AddressId = Addr2.Addressid LEFT OUTER JOIN
dbo.OccupancyTypes As OT2 on Addr2.OccupancyTypeId = OT2.OccupancyTypeId Right OUTER JOIN
dbo.v_Activities as VA2 on Addr2.AddressId = VA2.AddressId LEFT OUTER JOIN
dbo.InspectionTypes As IT2 on VA2.InspectionTypeId = IT2.InspectionTypeId
Where Addr.AddressCode <> Addr2.AddressCode and PA2.RoleType = 'Owner' and PA2.AddInact = 'False' and APInactive = 'False' And VA2.InspectionCauseId <> '4611aa3c-17c2-4421-81bb-3e973786f26d'
Order By PA.AddressCode, InspectionDate Desc, IT.InspectionType