Your SQL code would be far more legible if you formatted it in a SQL code block:
SELECT a.OBJECTID,
a.PIN,
a.Subtype,
b.parcel_no,
b.siteaddress,
b.site_citystzip,
b.sw_hauler,
b.sw_service_day,
b.sw_str_sweep_week,
a.GlobalID,
a.Shape,
b.own1
FROM dbo.parcel_polygon a
LEFT OUTER JOIN dbo.ParcelWeb b ON a.pin = b.pin
WHERE a.status != 'Retired'
and a.status != 'Pending_Retired'
and a.status != 'Pending'
From this vantage point, we can see:
- You've used "!=" not the ISO standard SQL "not equal" operator "<>"
- Your WHERE clause is an AND of several NOT EQUAL tests (which can't be effectively searched by as index)
So the first question is what are the valid values in a.status?
SELECT DISTINCT a.status FROM dbo.parcel_polygon a
And what are the table counts across the joins?
SELECT
(SELECT count(*) FROM dbo.parcel_polygon ) as acount,
(SELECT count(*) FROM dbo.ParcelWeb ) as bcount,
(SELECT count(*) FROM dbo.parcel_polygon
LEFT OUTER JOIN dbo.ParcelWeb b ON b.pin = a.pin) as jcount,
(SELECT count(*) FROM dbo.parcel_polygon a
WHERE field not in ('Retired','Pending_Retired','Pending')) as qcount
Debugging joins is a stepwise process, stripping out not required components to determine where your logic doesn't align with that of the database.
- V
