Mysql 3 way join error

Question

I am trying to create a 3 way join between marks, messages, and votes. I tried to model the other posts on this particular query, but it does not seem to work:

Error

[Error: ER_PARSE_ERROR: 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 'LEFT JOIN votes ON votes.messageId = messages.id ORDER BY timestamp DESC' at line 1]

Here is my query:

 var query = ([
    'SELECT * FROM marks',
    'LEFT JOIN messages',
    'ON marks.messageId = messages.id',
    'WHERE marks.x between ? AND ?',
    'AND marks.y between ? AND ?',
    'LEFT JOIN votes',
    'ON votes.messageId = messages.id',
    'ORDER BY timestamp DESC'
]).join(' ');

Schema

CREATE TABLE marks (
  id int(5) AUTO_INCREMENT,
  PRIMARY KEY (id),
  x float(10, 6),
  y float(10, 6),
  z float(10, 6),
  timestamp timestamp DEFAULT CURRENT_TIMESTAMP,
  messageId int(5),
  FOREIGN KEY (messageId) REFERENCES messages(id),
  userToken VARCHAR(255),
  FOREIGN KEY (userToken) REFERENCES users(token)
);

CREATE TABLE messages (
  id int(5) AUTO_INCREMENT,
  PRIMARY KEY (id),
  messageString text,
  score int(5) DEFAULT 0
);

CREATE TABLE votes (
  id int(5) AUTO_INCREMENT,
  PRIMARY KEY(id),
  userToken VARCHAR(255),
  FOREIGN KEY (userToken) REFERENCES users(token),
  messageId int(5),
  FOREIGN KEY (messageId) REFERENCES messages(id)
);

WHERE has to be after ALL the joins. – Norbert Apr 10 '15 at 18:58 — Norbert, Apr 10 '15 at 18:58

score 2 · Answer 1 · answered Apr 10 '15 at 19:00

2

Try this:

var query = ([
    'SELECT * FROM marks',
    'LEFT JOIN messages',
    'ON marks.messageId = messages.id',
    'LEFT JOIN votes',
    'ON votes.messageId = messages.id',
    'WHERE marks.x between ? AND ?',
    'AND marks.y between ? AND ?',
    'ORDER BY timestamp DESC'
]).join(' ');

The WHERE clause has to to be after the JOINS.

answered Apr 10 '15 at 19:00

Rahul Tripathi

168,305
31
280
331

2

@chopperdrawlion4:- You can use the `Group By Votes` and `sum(votes)`. If I understood it correctly. :) – Rahul Tripathi Apr 10 '15 at 19:11
So should the first line be: 'SELECT *, SUM(votes) FROM marks',? – chopper draw lion4 Apr 10 '15 at 19:15
1

@chopperdrawlion4:- Yes, You can give it a try. – Rahul Tripathi Apr 10 '15 at 19:15

score 1 · Answer 2 · answered Apr 10 '15 at 19:02

You need to put the join statements before your where filter, like so:

var query = ([
   'SELECT * FROM marks',
   'LEFT JOIN messages',
     'ON marks.messageId = messages.id',
   'LEFT JOIN votes',
     'ON votes.messageId = messages.id',
   'WHERE marks.x between ? AND ?',
   'AND marks.y between ? AND ?',
   'ORDER BY timestamp DESC'
]).join(' ');

score 0 · Answer 3 · answered Apr 10 '15 at 19:08

Your JOIN clauses have to be grouped together, and your WHERE clause has to be after both of them.

This should work for you:

var query = ([
  'SELECT * FROM marks',
  'LEFT JOIN messages',
  'ON marks.messageId = messages.id',
  'LEFT JOIN votes',
  'ON votes.messageId = messages.id',
  'WHERE marks.x between ? AND ?',
  'AND marks.y between ? AND ?',
  'ORDER BY timestamp DESC'
]).join(' ');

Mysql 3 way join error

3 Answers3