Comparing fields to find none matches

SELECT Left([PTC Import].[ZipCode],5) AS Expr1, [PTC Import].[Country], [ZipCodeDatabase_STANDARD].[ZipCode]
FROM [PTC Import] 
LEFT JOIN [ZipCodeDatabase_STANDARD].[ZipCode] ON Expr1 = [ZipCodeDatabase_STANDARD].[ZipCode]
WHERE ((([ZipCodeDatabase_STANDARD].[ZipCode]) Is Null) AND (([PTC Import].[Country])="USA"
FROM [PTC Import], ZipCodeDatabase_STANDARD;

Ok what I have here is my attempt to compare the ZipCode field from my import to our ZipCode database to validate and make sure it is in fact a valid zip code. I would like this query to display any records that do not match a valid zip code where the country is USA.

I am currently getting a syntax error in JOIN statement error message.

I have the ZipCode db linked from another Access db if that would affect anything.

Answers


The last line

FROM [PTC Import], ZipCodeDatabase_STANDARD;

shouldn't be there. I think you had an inner join and forgot to remove it:

Then you have an error in your LEFT JOIN with [ZipCodeDatabase_STANDARD], where you are using an alias you defined in FROM. You should use Left(i.[ZipCode],5), since the alias isn't defined yet.

Additionally, i added an alias for each table to make it easier to read.

It should be:

SELECT Left(i.[ZipCode],5) AS Expr1, 
       i.[Country], 
       zs.[ZipCode]
FROM [PTC Import] i 
LEFT JOIN [ZipCodeDatabase_STANDARD] zs ON Left(i.[ZipCode],5) = zs.[ZipCode]
WHERE zs.[ZipCode] Is Null AND i.[Country])="USA";

Your LEFT JOIN seems to be trying to join a table with a field.

FROM [PTC Import] 
LEFT JOIN [ZipCodeDatabase_STANDARD].[ZipCode]

Join a table to another table instead. Perhaps this is what you need.

FROM [PTC Import] 
LEFT JOIN [ZipCodeDatabase_STANDARD]

For the join's ON clause, I think you want ...

ON [PTC Import].[ZipCode] = [ZipCodeDatabase_STANDARD].[ZipCode]

And then eliminate the second FROM clause.

If you have Access' query designer available, use that to create your query. At least set up the join from the designer because it knows how to create joins which keep the db engine happy.


Need Your Help

finding second smallest value in mysql table

mysql

I have one mysql table with the following set of records.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.