help with my SQL Query, checking results against another table
Posted on 2013-01-17
Here is my command, what it does is find all cities within X miles of an existing city. It works just fine.
set @cmd = 'SELECT Distinct zips.CityName, zips.ProvinceAbbr
FROM dbo.postalcodes AS [zips]
INNER JOIN dbo.Calculateboundary(' + @StrLat + ', ' + @StrLon + ', ' + @Strradius + ', ''' + @unit + ''') AS [bounds]
WHERE [zips].[Latitude] BETWEEN [bounds].[South] AND [bounds].[North]
AND [zips].[Longitude] BETWEEN [bounds].[West] AND [bounds].[East]
AND [zips].[Latitude] <> 0
AND [zips].[Longitude] <> 0
AND dbo.CalculateDistance(' + @StrLat + ', ' + @Strlon + ', [zips].[Latitude], [zips].[Longitude], ''' + @unit + ''') <= ' + @Strradius + '
AND [zips].[CityType] = ''D'' and [zips].[active]=''1'''
This command above returns all CITY and STATE within X mile radius. (CityName and ProvinceAbbr)
So here is what I want to do. I have a table called CITIES and there is a column called "CITYOPEN" that is either a 1 or a zero. It also has matching columns of CITY and STATE. I want my command above to return a third column CITYOPEN and its corresponding value. The only catch is, that if a CITY, STATE returned in my first command does NOT exist in table CITIES (i.e. NULL), then CITYOPEN = 1.
CITYOPEN = 0 only happens when CITY, STATE exist in table CITIES and it is specifically set to 0, otherwise the value is always 1. Hope that makes sense!