Mysql 3路连接错误

Question

我正试图在标记、消息和投票之间创建一个3种方式的连接。我试图在这个特定的查询上建模the other posts，但它似乎不起作用：

误差

[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]

这里是我的查询：

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

模式

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

Answer 1

试试这个：

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

WHERE子句必须在JOINS之后。

Answer 2

您需要将join语句放在where筛选器之前，如下所示：

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

Answer 3

联接子句必须组合在一起，WHERE子句必须在两者之后。

这应该适用于你：

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