Solved

Select where not in another table

Posted on 2013-01-31
8
278 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 

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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

777 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