Solved

Select where not in another table

Posted on 2013-01-31
8
272 Views
Last Modified: 2013-02-03
I have 2 tables  
dbo.WideCityList  and dbo.PostalCodes.
I want to find all widecitylist.City, WideCityList.State that does not exist in postalCodes.Cityname, PostalCodes.ProvinceAbbr


All cities in WideCityList have a dash "-" where there should be a space.  
So in WideCity list it is "Los-Angeles" and in PostalCodes it is "Los Angeles"

So a city+state in WideCityList that does not exist in PostalCodes will need to have a replace function for any dashes into spaces.  Hope that makes sense!
0
Comment
Question by:arthurh88
8 Comments
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 250 total points
ID: 38839079
Try this:

SELECT w.City, w.State 
FROM WideCityList w LEFT JOIN postalCodes p
ON w.city  = p.Cityname AND w.State = p.ProvinceAbbr
WHERE p.Cityname IS NULL AND p.ProvinceAbbr IS NULL

Open in new window

0
 
LVL 18

Accepted Solution

by:
Cluskitt earned 250 total points
ID: 38839087
Two ways:
SELECT REPLACE(City,'-',' ') City,State
FROM WideCityList t
WHERE NOT EXISTS (SELECT NULL FROM PostalCodes WHERE REPLACE(City,'-','')=Cityname AND State=ProvinceAbbr)

OR

SELECT REPLACE(City,'-',' ') City,State
FROM WideCityList t
LEFT JOIN PostalCodes
ON REPLACE(City,'-','')=Cityname AND State=ProvinceAbbr
WHERE Cityname IS NULL
0
 
LVL 5

Expert Comment

by:RehanYousaf
ID: 38839255
If you provide with some sample data and desired result ... it will help
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:arthurh88
ID: 38840437
that worked great, thank you
0
 

Author Comment

by:arthurh88
ID: 38840781
oops, meant to also give credit to you Cluskitt, because you had the replace function i needed.
0
 

Expert Comment

by:zombie2020
ID: 38842492
Another solution:

select replace(widecitylist.City, '-',' ') ,
            replace(WideCityList.State, '-','')
from dbo.WideCityList
except
select postalCodes.Cityname,
            PostalCodes.ProvinceAbbr
from dbo.PostalCodes;
0
 

Author Closing Comment

by:arthurh88
ID: 38849098
it was all very helpful to me, i split credit.  thank you and my apologies
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now