How to pull data

Question

We have these 2 tables - places and ads

places
+---------+--------------+-----------+
| PlaceID |  CountryCode |    Name   |
+---------+--------------+-----------+
|       1 |  AU          |  Sydney   |
|       2 |  CA          |  Sydney   |
|       3 |  US          |  Miami    |
|       4 |  AU          |  Canberra |
+---------+--------------+-----------+

ads
+------+--------+-----------+
| AdID |  Name  |  Location |
+------+--------+-----------+
|    1 |  Test  |  Canberra |
|    2 |  Test2 |  Sydney   |
|    3 |  Test3 |  null     |
|    4 |  Test4 |  Delhi    |
|    5 |  Test5 |  Miami    |
+------+--------+-----------+

We need to pull the Ads data that is NOT belong to Australia based on this country. However under Ads table there is no country specified. THe dilemma is actually because Sydney are belong 2 countries we would like NOT to include AdID=2 eventhough is Sydney.

Outcome:

+------+--------+-----------+
| AdID |  Name  |  Location |
+------+--------+-----------+
|    4 |  Test4 |  Delhi    |
|    5 |  Test5 |  Miami    |
+------+--------+-----------+

We could join this 2 tables but not sure how NOT to include this "Sydney". Again Sydney is example BUT there will be hundreds city name that belong to more than 1 country.

What is the right query to pull out this outcome?

Answer 1

select a.*
from places p
inner join ads a on p.Name=a.Location
where p.CountryCode<>'AU'

Edited based on edited question and requirement

select *
from ads
where isnull(Location,'') not in 
(select isnull(Name,'') from places where CountryCode<>'AU')
and isnull(Location,'')<>''

SQL Fiddle

Answer 2

Given the structure in your question, you cannot achieve what you want. It's completely illogical, and unfortunately impossible. How do you expect to make an association between an ad and a country when you haven't defined the relationship?

Your two tables currently have no relationship. Your tables should have looked like this:

locations
+----+---------+--------+
| id | country |  city  |
+----+---------+--------+
|  1 | AU      | Sydney | <----+
|  2 | CA      | Sydney | <--+ |
+----+---------+--------+    | |
                             | |
ads                          | |
+----+------+------------+   | |
| id | name | locationId |   | |
+----+------+------------+   | |
|  1 | Test |          2 | <-+ |
|  2 | Test |          1 | <---+
+----+------+------------+

This way, ads could have been associated with locations as a whole, which would include the country and city.