question

PaulBill-8748 avatar image
0 Votes"
PaulBill-8748 asked MelissaMa-msft commented

Help with a Union Select statement

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

sql-server-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

GuoxiongYuan-7218 avatar image
0 Votes"
GuoxiongYuan-7218 answered GuoxiongYuan-7218 edited

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Viorel-1 avatar image
0 Votes"
Viorel-1 answered Viorel-1 edited

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…


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

PaulBill-8748 avatar image
0 Votes"
PaulBill-8748 answered MelissaMa-msft commented

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?

· 7
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Which table has the column InspectionDate?

0 Votes 0 ·

It's the v_Activities table

0 Votes 0 ·

Create a CTE to filter out records from the table v_Activities to make sure data with latest InspectionDate is selected. And then you replace the table with the CTE when you Right OUTER JOIN it. I updated the code. Since I do not know how many columns you need from the table v_Activities, I just add some as the example. You may need to add more.

0 Votes 0 ·
Show more comments