MySQL 在mysql中使用where和inner join
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1392479/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
using where and inner join in mysql
提问by sgi
I have three tables.
我有三张桌子。
locations
地点
ID | NAME | TYPE |
1 | add1 | stat |
2 | add2 | coun |
3 | add3 | coun |
4 | add4 | coun |
5 | add5 | stat |
schools
学校
ID | NAME
1 | sch1
2 | sch2
3 |sch3
school_locations
school_locations
ID |LOCATIONS_ID |SCHOOL_ID
1 | 1 |1
2 | 2 |2
3 | 3 |3
Here the table locations contains all the locations of the application.Locations for school are called by ID's.
这里的表位置包含应用程序的所有位置。学校的位置由 ID 调用。
when i use the query
当我使用查询时
select locations.name from locations where type="coun";
it displays names with type "coun"
它显示类型为“coun”的名称
But I want to display locations.name where only school_locations have type="coun"
但我想显示只有 school_locations 有 type="coun" 的locations.name
i tried following queries, but none seems to be working
我尝试了以下查询，但似乎都没有工作
select locations.name
from locations
where type="coun"
inner join school_locations
on locations.id=school_locations.location_id
inner join schools
on school_locations.school.id=schools.id;
and
和
select locations.name
from locations
inner join school_locations
on locations.id=school_locations.location_id
inner join schools
on school_locations.school.id=schools.id where type="coun";
is it possible to use multiple inner joins in queries, or is there another way?
是否可以在查询中使用多个内部联接，或者还有其他方法吗？
回答by knittl
SELECT `locations`.`name`
FROM `locations`
INNER JOIN `school_locations`
ON `locations`.`id` = `school_locations`.`location_id`
INNER JOIN `schools`
ON `school_locations`.`school_id` = `schools_id`
WHERE `type` = 'coun';
the WHEREclause has to be at the end of the statement
该WHERE子句必须在语句的末尾
回答by David Andres
Try this:
尝试这个：
SELECT Locations.Name, Schools.Name
FROM Locations
INNER JOIN School_Locations ON School_Locations.Locations_Id = Locations.Id
INNER JOIN Schools ON School.Id = Schools_Locations.School_Id
WHERE Locations.Type = "coun"
You can join Locations to School_Locations and then School_Locations to School. This forms a set of all related Locations and Schools, which you can then widdle down using the WHERE clause to those whose Location is of type "coun."
您可以将 Locations 加入 School_Locations，然后将 School_Locations 加入 School。这形成了一组所有相关的 Locations 和 Schools，然后您可以使用 WHERE 子句将它们混合到 Location 类型为“coun”的那些。
回答by Hadi
Try this :
尝试这个 ：
SELECT
(
SELECT
`NAME`
FROM
locations
WHERE
ID = school_locations.LOCATION_ID
) as `NAME`
FROM
school_locations
WHERE
(
SELECT
`TYPE`
FROM
locations
WHERE
ID = school_locations.LOCATION_ID
) = 'coun';
回答by baldy
You can use as many joins as you want, however, the more you use the more it will impact performance
您可以根据需要使用任意数量的连接，但是，使用的越多，它对性能的影响就越大
