Join 1 field of a table to 2 based on criteria

Firstly, let's deal with why your present design is so wrong.  Before turning to specifics it's important to understand that the database relational model is a formal model and governed by formal rules.  One of the most basic of these is the Guaranteed Access Rule, which is stated as follows:

Every datum (atomic value) in a relational database is guaranteed to be logically accessible by resorting to a combination of table name, primary key value and column name.

This is rule #2 in the twelve rules published by E F Codd in the paper "Is Your DBMS Really Relational?" in ComputerWorld on14 October 1985.  It was Codd who introduced the database relational model to the world in his seminal paper in 1970.

What this means of course is that each column in a table can contain values of one attribute only, and those values must be legitimate values of the single attribute type which the column represents.  In your case, however, the column in your Location table contains values of two different attributes, city and state, and consequently violates the Guaranteed Access Rule.  To see how what you are attempting should be represented let's turn to my DatabaseBasics demo.  In this you'll find the following tables:

Cities

CityID  City              RegionID

1          Stafford       1

5          Paris           3

6          Paris           4

7          Lichfield      1

8          Newcastle  1

9          Derby         5

10        Stratford     6

11        Monaco      7

Regions

RegionID         Region                  CountryID

1                      Staffordshire         1

3                      Texas                    2

4                      Île-de-France        3

5                      Derbyshire            1

6                      Warwickshire        1

7                      N/A                        4

8                      California               2

9                      Pays de la Loire    3

Countries

CountryID    Country

1                   United Kingdom

2                   United States

3                   France

4                   Monaco

If the three tables are joined in a query the following result table can be returned:

City                      Region                    Country

Stafford               Staffordshire           United Kingdom

Lichfield               Staffordshire           United Kingdom

Newcastle           Staffordshire           United Kingdom

Paris                   Texas                       United States

Paris                    Île-de-France          France

Derby                   Derbyshire              United Kingdom

Stratford               Warwickshire         United Kingdom

Monaco                N/A                          Monaco

For all but one of the rows the city relates to a region, and the region to a country.  In the case of Monaco, however, the country has no regions, so how do we represent the fact that the city of Monaco is in the country of Monaco?  The answer is by this row in the Regions table:

RegionID         Region              CountryID

7                      N/A                    4

As you can see the 'region' N/A is referenced in cities by the value 7 in the RegionID foreign key column, which references RegionID 7 of the primary key in the above row.  The N/A row is therefore plugging the gap in the discontinuous hierarchy which results from Monaco having no regions.

In the above model it is assumed that we know which region any city is in, so the N/A row is only necessary in the case of small countries like Monaco which have no regions.  However, we might want to be able represent which country a city is in if we don't know which region it is in.  To do this we would extend the Regions table as follows:

Regions

RegionID         Region                  CountryID

1                      Staffordshire         1

3                      Texas                    2

4                      Île-de-France        3

5                      Derbyshire            1

6                      Warwickshire        1

7                      N/A                        4

8                      California               2

9                      Pays de la Loire    3

10                    Unknown               1

11                    Unknown               2

12                    Unknown               3

We now have an Unknown region for every country which has a regional structure and one N/A region for the country that doesn't, so, if in the UK for instance we need to enter the city Wakefield, but do not know which region it is in we'd enter the following row in Cities:

CityID      City              RegionID

12            Wakefield    10

In the query which joins the tables this would return:

City               Region        Country

Wakefield     Unknown     United Kingdom

Your situation is analogous.  The column in your Location table should reference only the city column in your Core table, and you should add rows with a value in the city column of 'Unknown' for each state.  This means of course that, if it contains city names, the city column cannot be a candidate key of the Core table because of the duplicated 'Unknown' values.  Even without those rows, however, a column of city names cannot be a key, because city names can legitimately be duplicated (think how many Springfields there are in the USA, or even Staffords, of which there are four, all deriving their name from the original Stafford where I live).  Cities must be identified by a distinct key value, which is usually a number, but can be any coding system you wish provided that the values are distinct.