在MySQL中使用where和内部联接 [英] using where and inner join in mysql

问题描述

我有三个桌子.

位置

ID   | NAME | TYPE |
1    | add1 | stat |
2    | add2 | coun | 
3    | add3 | coun |
4    | add4 | coun | 
5    | add5 | stat | 

学校

 ID | NAME  
 1  | sch1     
 2  | sch2
 3  |sch3 

学校所在地

 ID |LOCATIONS_ID |SCHOOL_ID
 1  | 1           |1
 2  | 2           |2
 3  | 3           |3

这里的表位置包含应用程序的所有位置.学校的位置由ID调用.

Here the table locations contains all the locations of the application.Locations for school are called by ID's.

我使用查询时

select locations.name from locations where type="coun";

它显示类型为"coun"的名称

it displays names with type "coun"

但是我想显示location.name，其中只有school_locations的类型为"coun"

But I want to display locations.name where only school_locations have type="coun"

我尝试了以下查询，但似乎没有任何作用

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;

和

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?

推荐答案

    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';

WHERE子句必须位于语句的末尾

the WHERE clause has to be at the end of the statement

这篇关于在MySQL中使用where和内部联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！