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
2
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
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;
#3
1
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
2
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
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;
#3
1
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