[RESOLVED] Left Join? Left Outer Join? Inner Join? @#$!^

jjfletch

I'm confused...

I have two tables: contacts and locations.

In contacts, the fields are:

Name
Address
City
State
Zip

In locations, the fields are:

City
County

(Table structure has been greatly simplified)

I want to write a query that will, for example, find all the contacts in any given city from contacts and lookup and return the county in which the city is located from locations.

So, if I search for contacts in San Jose, I want the script to lookup San Jose in the locations database and return a record that will look like this:

Joe Schmoe
123 Abcdefg Street
San Jose, CA 99999
Santa Clara County

Is this possible? Do I use a JOIN statement? Can someone provide an example?

I did try the following:

SELECT name, address, contacts.city, state, zip, locations.city FROM contacts
        LEFT JOIN locations USING(city)
        where city LIKE ('%$cy%')
        ORDER BY name ASC;

But get this error:

ERROR 1052: Column: 'city' in where clause is ambiguous

I also tried to just insert a 'county' column in contacts and update this column with values from locations thinking maybe that's the easiest way. I tried this code:

update contacts set contacts.county=locations.county where contacts.city like locations.city;

but got this:

ERROR 1109: Unknown table 'locations' in where clause

Any suggestions or corrections to the code?