MySQL query selecting values from two tables

Question

I have the following two tables:

Cities: id | name | county_id
Counties: id | name

I am passing this query a county 'name' and I am trying to select id and name of all rows from cities with the same county_id. I am having difficulty building a query across these two database tables. Here is what I have:

"SELECT cities.id,cities.name
FROM cities,counties
WHERE counties.name='$county' AND cities.county_id=counties.id
ORDER BY cities.name ASC";

Answer 1

SELECT cities.id,cities.name
FROM Counties INNER JOIN CITIES 
ON Counties.id = Cities.county_id
WHERE  counties.name='$county'
ORDER BY cities.name ASC;

Answer 2

Presumably you're using mysql_query("..."), so drop an echo mysql_error(); line immediately below the query and see whether it's failing for some reason. I reckon you've not connected properly in this case, and the original query looks fine (albeit using the old join syntax - better syntax given below)...

SELECT ci.id, ci.name
FROM  cities ci
INNER JOIN counties co
ON ci.county_id = co.id
WHERE co.name='$county'
ORDER BY ci.name ASC

Answer 3

If you're running on Unix and if I take your question literally, your problem may be with case-sensitivity.

Table names are case-sensitive in Unix, so if your tables are called Counties and Cities, then you have a problem when you use counties and cities in your SQL query.

That said, Will A's response regarding mysql_error() is crucial... have your code print out the error and post it here if you need further assistance.