I'm having a problem joining to zipcodes

[archwisp]

The problem I am having is with a database application I'm writting using MSSQL & ASP. I have a list of locations with standard address information. I also have a list that breaks up all the zipcodes we have into territories then annother list that links representatives to each territory.

The querry I have bellow acctualy lists all the zipCodes that dont have a territory assigned to them.

Okay, here's the query:

1: SELECT DISTINCT
2: locationList.state, locationList.city, locationList.zipCode,
3: dtpTerrList.terrNum AS dtpTerr, COUNT(*) AS COUNT
4: FROM locationList
5: LEFT OUTER JOIN dtpZipDesigList ON dtpZipDesigList.zipCode = locationList.zipCode
6: LEFT OUTER JOIN dtpTerrList ON dtpTerrList.dtpTerrId = dtpZipDesigList.fkDtpTerrId
7: WHERE dtpTerrList.terrNum IS NULL
8: GROUP BY locationList.state, locationList.city, locationList.zipCode, dtpTerrList.terrNum
9: ORDER BY locationList.state, locationList.city, locationList.zipCode DESC

The problem :

See line 5?

5: LEFT OUTER JOIN dtpZipDesigList ON dtpZipDesigList.zipCode = locationList.zipCode

The zip codes in each field are varchar type (I've tried char and nvarchar). When it does the comparrison it doesnt properly link zip codes that have a leading '0'. (Exa: 01874).


Does anyone have a solution to this in MSSQL 2000? Should I be using a different field type? Or is there a way to specify that you want a string comparrison in SQL?

Thanks a ton. (sorry for the spelling)

Bryan Geraghty

 

[tlbroadbent]

Bryan,

Post your question in forum183, the SQL Server forum. There is no reason for the join to fail if the columns are both character data types and don't have leading or trailing characters. Are they the same size columns? Does one table have Zip+4 while the other doesn't? When you post in the SQL Server forum, post a few examples from the tables and the column definitions - data type and length. If you want to get the best answer for your question read faq183-874 and thread183-468158.


Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm