MySQL - 在同一查询中使用多个LEFT JOIN语句

Question

I am trying to select data from multiply tables using MySQL. Therefore I am using LEFT JOIN. Although I am having trouble getting the statement correct.

This is my code:

$stmt = $dbh->prepare("
            SELECT ur.title, ur.forum_id, urs.*
            FROM forum_topics as ur 
              LEFT JOIN forum_cats as urs 
              ON ur.forum_id=urs.forum_id 
            WHERE ur.topic_id=:topicid
              LEFT JOIN forum_posts as pos
            WHERE pos.post_id=:post



            "
         );
    $stmt->bindParam(':topicid',$postData['topic_id']);
    $stmt->bindParam(':post', $post);
    $stmt->execute();
    $topicData = $stmt->fetch();

This is my error message:

<b>Fatal error</b>:  Uncaught exception 'PDOException' with message '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 'LEFT JOIN forum_posts as pos
WHERE pos.post_id='5886'
' at line 6' .php</b> on line <b>82</b><br />

So, my question is: what is wrong with my MySQL Query?

Answer 1

You should use one WHERE condition and specify the join condition with ON in the second join:

SELECT 
  ur.title, 
  ur.forum_id, 
  urs.*
FROM forum_topics as ur 
LEFT JOIN forum_cats as urs 
  ON ur.forum_id=urs.forum_id 
LEFT JOIN forum_posts as pos
  ON (... condition ...)
WHERE ur.topic_id=:topicid
  AND pos.post_id=:post

Note I left out the join condition since it's not clear from the question and I guessed you wanted an AND in the WHERE clause, you can switch to OR if needed.