?
Solved

Select where not in another table

Posted on 2013-01-31
8
Medium Priority
?
289 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
7 Comments
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 1000 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 1000 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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 

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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

809 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