MySQL - 1064 issue with multiple joins

Question

Here is a brief summary of my database tables involved:

Courses

CourseId
SchoolId
DisciplineId
Name

Schools

SchoolId
Name

Disciplines

DisciplineId
Name

(there are other columns in the tables, but they're not really relevant to the question)

I'm trying to select all of the Courses where Courses.SchoolId = a given SchoolId, the name of said school from Schools, the names of all of the disciplines associated with each course, and sort by Disciplines.Name. If this summary doesn't make sense, perhaps my query will:

 $prep = $pdo->prepare('SELECT Courses.*, Disciplines.Name as Discipline, Schools.Name as School
                        FROM Courses
                        JOIN Courses AS C ON Courses.SchoolId = :schoolId
                        AND Schools ON Schools.SchoolId = :schoolId
                        AND Disciplines ON Disciplines.DisciplineId = Courses.DisciplineId
                        ORDER BY Disciplines.Name ASC');
$prep->bindParam(':schoolId', $schoolId, PDO::PARAM_INT);

if(!$prep->execute()){
    print_r($pdo->ErrorInfo());
}

Here is the error:

Warning: PDOStatement::execute(): SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ON Schools.SchoolId = '115773' AND Disciplines ON Disciplines.Disciplin' at line 4 in /Users/foremanjordan/Web Dev/Cleerly/src/scripts/addCourse.php on line 24

I cannot for the life of me figure out why this is. I'm still fairly new to using JOINs, so I'm sure that it has something to do with my ignorance, but from what I've understood thus far, this should work?

Also, as a sort of side question, does anyone understand why I would have to alias Courses in the first JOIN line? I get a non-unique error otherwise, but I don't understand why.

Thank you!

Post the ***full*** error and relevant code. – Kermit Apr 12 '13 at 03:24 — Kermit, Apr 12 '13 at 03:24
added the full error/code – Jordan Foreman Apr 12 '13 at 03:28 — Jordan Foreman, Apr 12 '13 at 03:28

score 2 · Accepted Answer · answered Apr 12 '13 at 03:27

2

use the specified keyword JOIN not AND

SELECT Courses.*, 
       Disciplines.Name as Discipline, 
       Schools.Name as School
FROM   Courses
       INNER JOIN Schools 
          ON Courses.SchoolId = Schools.SchoolId
       INNER JOIN Disciplines 
          ON Disciplines.DisciplineId = Courses.DisciplineId
WHERE  Courses.SchoolId = :schoolId
ORDER  BY Disciplines.Name ASC

When sorting, use ORDER BY not SORT

To further gain more knowledge about joins, kindly visit the link below:

Visual Representation of SQL Joins

answered Apr 12 '13 at 03:27

John Woo

258,903
69
498
492

Thanks! That worked like a charm! I'll have to read that article. Most of what I found was fairly difficult to grasp, even though I understand the concept, and so I've been trying to learn by doing w/ trial and error. Also, thanks for pointing out the `SORT` thing. I haven't been writing queries for a long time, and keep forgetting my basic syntax! – Jordan Foreman Apr 12 '13 at 03:34

MySQL - 1064 issue with multiple joins

1 Answers1