Mr. Robot February 2016

MySQL Syntax Error for a complex query

Here is the complex query that I wrote but can't figure out what's the problem:

SELECT student.progid,
   student.batch
FROM   (student
    JOIN registers
      ON student.studentid = registers.studentid)
   JOIN (SELECT offers.courseno
         FROM   (offers
                JOIN instructor
                  ON offers.instructorid = instructor.instructorid))
     ON offers.courseno = registers.courseno
WHERE  instructor.instructorname = 'P M Jaat'
   AND ( offers.acadyear LIKE '2007%'
          OR offers.acadyear LIKE '2008%'
          OR offers.acadyear LIKE '2009%'
          OR offers.acadyear LIKE '2010%'
          OR offers.acadyear LIKE '2011%' ); 

This results in an error, but I'm going to leave it to others to tell you what that is:

ERROR 1064 (42000): 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 'R1
    JOIN (SELECT 

Answers


Rocket Hazmat February 2016

Double check your parenthesis. Your FROM clause is

(student JOIN registers ON student.StudentID=registers.StudentID)

Which doesn't make sense.

What you want is:

FROM (student)
JOIN registers AS r1
    ON (student.StudentID=registers.StudentID)

Or you can just lose the extra parenthesis:

FROM student
JOIN registers AS r1
    ON student.StudentID=registers.StudentID

P.S. You also had your AS r1 in the wrong spot.

UPDATE: You need parenthesis when you are using subqueries.

FROM student
JOIN registers
    ON student.studentid = registers.studentid
JOIN(
    SELECT courseno
    FROM offers
    JOIN instructor
    ON offers.instructorid = instructor.instructorid
) AS r2 ON offers.courseno = registers.courseno


Strawberry February 2016

Perhaps you're after something like this...

    SELECT r.progid
         , r.batch
      FROM student s
      JOIN registers r
        ON r.studentid = s.studentid 
      JOIN offers o
        ON o.courseno = r.courseno
      JOIN instructor i
        ON i.instructorid = o.instructorid  
     WHERE i.instructorname = 'P M Jaat' 
       AND o.acadyear BETWEEN '2007-01-01' AND '2011-12-31';

Post Status

Asked in February 2016
Viewed 3,519 times
Voted 11
Answered 2 times

Search




Leave an answer