MySQL查询从两个表中选择值。

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:

我将此查询传递给一个县的“名称”，并尝试从具有相同county_id的城市中选择所有行的id和名称。我很难跨这两个数据库表构建查询。以下是我的资料:

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

3 个解决方案

#1

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

如果你在Unix上运行，如果我逐字回答你的问题，你的问题可能是大小写敏感的。

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.

在Unix中，表名是区分大小写的，因此，如果您的表被称为郡县和城市，那么当您在SQL查询中使用县和城市时，就会出现问题。

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.

也就是说，A对mysql_error()的响应是至关重要的吗?让您的代码打印出错误，如果您需要进一步的帮助，请在这里发布。

#2

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;

#3

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)...

假设您正在使用mysql_query(“…”)，因此删除echo mysql_error();直接在查询下面行，看看它是否因为某种原因失败。我认为在这种情况下，您没有正确地连接，并且原始查询看起来很好(尽管使用了旧的连接语法—下面给出的更好的语法)……

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

#1