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

时间:2021-03-10 15:39:20

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