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
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.
when i use the query
select locations.name from locations where type="coun";
it displays names with type "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;
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?
This question is related to
mysql
inner-join
You can use as many joins as you want, however, the more you use the more it will impact performance
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';
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."
Source: Stackoverflow.com