Help with a Union Select statement

Question

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

Answer 1

I do not think you can use Addr.AddressCode <> Addr2.AddressCode when you use UNION since the alias Addr belongs to the first SELECT statement. Try this:

;WITH CTE_v_Activities AS (
    SELECT *
    FROM (
        SELECT AddressId, InspectionType, InspectionCauseId, InspectionDate, ..., ROW_NUMBER() OVER(PARTITION BY InspectionType, ... ORDER BY InspectionDate DESC) AS RowNumber
        FROM dbo.v_Activities 
    ) AS t
    WHERE t.RowNumber = 1
),
CTE_01 AS (
    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
    CTE_v_Activities AS 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'
),
CTE_02 AS (
    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
    CTE_v_Activities as VA2 on Addr2.AddressId = VA2.AddressId LEFT OUTER JOIN
    dbo.InspectionTypes As IT2 on VA2.InspectionTypeId = IT2.InspectionTypeId
    Where PA2.RoleType = 'Owner' and PA2.AddInact = 'False' and APInactive = 'False' And VA2.InspectionCauseId <> '4611aa3c-17c2-4421-81bb-3e973786f26d'
) 

SELECT * FROM CTE_01
UNION
SELECT * FROM CTE_02 WHERE AddressCode NOT IN (SELECT AddressCode FROM CTE_01)
ORDER BY AddressCode, InspectionDate Desc, InspectionType

Answer 2

Check if this works:

select distinct * from
(
   SELECT…
   UNION ALL
   SELECT… -- without additional condition
) t

Also check if you can use a single query (without UNION):

select … where PA.RoleType in ('Property Contact', 'Owner') and…

Answer 3

Thanks, GuoxiongYuan-7218.

That's almost got it. My results are showing multiple records for an address with the same InspectionType due to their being multiple inspectiondates of that type. How would I now limit the results down to the most recent inspectiondate for the inspectiontype at each address?