DECLARE @RegionID AS Integer = ? ; /*results.asp case=Region*/ INSERT INTO #ResultsTemp (MarkerID, Marker, Memorial) SELECT MarkerID, Marker, Memorial FROM Markers WHERE Accepted Is Not Null AND Withdrawn Is Null AND Rejected Is Null AND ReplacedByID Is Null AND MarkerID IN ( SELECT m.MarkerID FROM RegionList rl JOIN Markers m ON m.Country = rl.Country WHERE rl.RegionID = @RegionID AND m.Accepted IS NOT NULL AND m.Rejected IS NULL AND m.Withdrawn IS NULL AND m.ReplacedByID IS NULL AND rl.AllCountry = 1 UNION ALL SELECT m.MarkerID FROM RegionList rl JOIN Markers m ON m.Country = rl.Country WHERE rl.RegionID = @RegionID AND m.Accepted IS NOT NULL AND m.Rejected IS NULL AND m.Withdrawn IS NULL AND m.ReplacedByID IS NULL AND rl.AllState = 1 AND m.State = rl.State UNION ALL SELECT m.MarkerID FROM RegionList rl JOIN Markers m ON m.Country = rl.Country WHERE rl.RegionID = @RegionID AND m.Accepted IS NOT NULL AND m.Rejected IS NULL AND m.Withdrawn IS NULL AND m.ReplacedByID IS NULL AND rl.AllCountry = 0 AND rl.AllState = 0 AND rl.Zip = '' AND rl.Town = '' AND rl.Neighborhood = '' AND m.State = rl.State AND m.County = rl.County UNION ALL SELECT m.MarkerID FROM RegionList rl JOIN Markers m ON m.Country = rl.Country WHERE rl.RegionID = @RegionID AND m.Accepted IS NOT NULL AND m.Rejected IS NULL AND m.Withdrawn IS NULL AND m.ReplacedByID IS NULL AND rl.Town <> '' AND rl.Neighborhood = '' AND m.State = rl.State AND m.Town = rl.Town AND rl.Zip = '' AND (rl.County = '' OR m.County = rl.County) UNION ALL SELECT m.MarkerID FROM RegionList rl JOIN Markers m ON m.Country = rl.Country WHERE rl.RegionID = @RegionID AND m.Accepted IS NOT NULL AND m.Rejected IS NULL AND m.Withdrawn IS NULL AND m.ReplacedByID IS NULL AND rl.Neighborhood <> '' AND m.State = rl.State AND m.Town = rl.Town AND m.Neighborhood = rl.Neighborhood AND rl.Zip = '' AND (rl.County = '' OR m.County = rl.County) UNION ALL SELECT m.MarkerID FROM RegionList rl JOIN Markers m ON m.Country = rl.Country WHERE rl.RegionID = @RegionID AND m.Accepted IS NOT NULL AND m.Rejected IS NULL AND m.Withdrawn IS NULL AND m.ReplacedByID IS NULL AND rl.AllCountry = 0 AND rl.AllState = 0 AND rl.Country = 'United States of America' AND LEN(rl.Zip) > 4 AND m.Zip = rl.Zip UNION ALL SELECT m.MarkerID FROM RegionList rl JOIN Markers m ON m.Country = rl.Country WHERE rl.RegionID = @RegionID AND m.Accepted IS NOT NULL AND m.Rejected IS NULL AND m.Withdrawn IS NULL AND m.ReplacedByID IS NULL AND rl.AllCountry = 0 AND rl.AllState = 0 AND rl.Zip <> '' AND m.Zip <> '' AND (rl.Country <> 'United States of America' OR LEN(rl.Zip) <= 4) AND m.Zip LIKE rl.Zip + '%' )