using where and inner join in mysql

Question

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?

knittl · Accepted Answer · 2009-09-08T09:39:32.393

69

    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 WHERE clause has to be at the end of the statement

edited Sep 08 '09 at 09:39

answered Sep 08 '09 at 07:28

knittl

246,190
53
318
364

are the backticks needed in msql? – Preet Sangha Sep 08 '09 at 07:34
2

no, but i like having them there, it avoids problems with strange table or column names (e.g. select, count, a%b, etc.) and is more foolproof – knittl Sep 08 '09 at 07:39
6

It also, unfortunately, makes the query non-ANSI-compliant, potentially causing problems if you try to migrate the query to another database. The proper way of delimiting identifiers is with "doublequotes", but MySQL doesn't support this by default; you have to set ANSI_QUOTES in SQL_MODE to get that. Of course that then messes up your string literal "coun", which should regardless of SQL_MODE be written with single quotes. – bobince Sep 08 '09 at 09:25
interesting, didn’t know that about backticks vs. quotation marks (i only work with mysql). and uh, you’re right about single quotes for strings (copy-paste error from the question asker’s code) – knittl Sep 08 '09 at 09:39
Gee, I was stuck putting the `WHERE` statement before the `INNER JOIN`. I used regular single quotes. – megamaiku Jul 12 '17 at 22:51
To add to this, if you are joining large tables you can run this as an `INNER JOIN (SELECT * WHERE type = 'coun') AS subset ON locations.id = school_locations.location_id` to first grab a subset of the large table being joined, presuming there's some criteria you can use in the WHERE clause. – Slam Aug 04 '17 at 00:01

score 4 · Answer 2 · answered Sep 08 '09 at 07:29

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."

score 1 · Answer 3 · edited Oct 02 '12 at 08:41

1

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

edited Oct 02 '12 at 08:41

Stephan

41,764
65
238
329

answered Aug 16 '12 at 16:39

Hadi

11
1

score -1 · Answer 4 · answered Sep 08 '09 at 07:29

-1

You can use as many joins as you want, however, the more you use the more it will impact performance

answered Sep 08 '09 at 07:29

baldy

5,524
4
22
19

3

that doesn’t answer his question – knittl Sep 08 '09 at 09:22
1

I beg to differ, see the last sentence in the question. Sure I didn't answer the whole question, but I felt that one of the other answers covered that. – baldy Sep 15 '09 at 07:23
2

But then according to stackoverflow etiquette this should be a comment to that particular answer. Thanks for understanding. – Harshay Buradkar Feb 06 '13 at 03:39

using where and inner join in mysql

4 Answers4

Linked