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

问题描述：

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?

答

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.

答

Use WHERE after all the joins, on all of the joined tables. Use ON or USING on all joins.

USING is a nice shorthand where the columns are named the same in both tables. It's not only shorter it also removes the duplicate column and makes it possible to, in this case, to use the column forum_id without table prefix.

Like this:

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

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

相关推荐