mySql Error in Query

Question

I have (3) tables: Professor, Comment, Course

I'm trying to get a few columns to display the professor with all the courses and all the comments they have.

My tables enter image description here

comment

enter image description here

SQL:

  SELECT prefix, code, info, date
        FROM Course, Comment
        JOIN Professor ON Professor.pID = Comment.pID
        AND JOIN Course ON Course.cID = Comment.cID
        WHERE Comment.pID = ?

Throws error: 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 'JOIN Course ON Course.cID = Comment.cID WHERE Comment.pID = '273'' at line 4

Rasika · Accepted Answer · 2011-04-05 23:45:20Z

2

SELECT prefix, code, info, date
    FROM Comment
    JOIN Professor ON Professor.pID = Comment.pID
    JOIN Course ON Course.cID = Comment.cID
    WHERE Comment.pID = ?

Just remove the AND before the second JOIN and remove Course in the From list. You may need to change the order of the tables depending on what table data you are primarily after.

Advise that you have a read through the MySQL JOIN syntax page.

edited Apr 5, 2011 at 23:45

answered Apr 5, 2011 at 23:40

Rasika

1,99814 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jshee Over a year ago

Now it throws error: Syntax error or access violation: 1066 Not unique table/alias: 'Course'' in

Rasika Over a year ago

Fixed. Remove the Course table in the From part.

Richard Schneider · Accepted Answer · 2011-04-05 23:42:33Z

0

change 'AND JOIN' to just 'JOIN'

answered Apr 5, 2011 at 23:42

Richard Schneider

35.5k9 gold badges59 silver badges74 bronze badges

Comments

Lightness Races in Orbit · Accepted Answer · 2011-04-05 23:44:31Z

0

There is no AND JOIN. Please read about JOIN syntax in the manual before attempting to use it.

Additionally, you reference table Course twice.

answered Apr 5, 2011 at 23:44

Lightness Races in Orbit

387k77 gold badges671 silver badges1.1k bronze badges

Collectives™ on Stack Overflow

mySql Error in Query

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related