MySQL - Using multiply LEFT JOIN statements in the same query

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?

You should use one WHERE condition and join conditions using AND or OR. — Ende Neu
– Ende Neu, Commented Aug 26, 2014 at 16:05
AND or OR conditions to specify what data to select? Could you provide an example based on my code? — oliverbj
– oliverbj, Commented Aug 26, 2014 at 16:06
What is the joining condition to forum_posts? To which other table does it related, and by what common column? You need to specify an ON clause for that join... — Michael Berkowski
– Michael Berkowski, Commented Aug 26, 2014 at 16:08
@MichaelBerkowski It is not related to the other tables - I though using the JOIN functions, I would save to do another query (and therefore another connection to the database) — oliverbj
– oliverbj, Commented Aug 26, 2014 at 16:10
@oliverbj No, you can't join without a relationship (well you can, but you get a cartesian product of all tables, which is probably not what you want). You have no columns from pos in the SELECT list, so if not to join, what did you hope to get from it? — Michael Berkowski
– Michael Berkowski, Commented Aug 26, 2014 at 16:11

Ende Neu · Accepted Answer · 2014-08-26 16:09:41Z

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.

answered Aug 26, 2014 at 16:09

Ende Neu

15.8k5 gold badges61 silver badges72 bronze badges

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

Comments

Andreas Wederbrand · Accepted Answer · 2014-08-26 16:10:32Z

0

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

answered Aug 26, 2014 at 16:10

Andreas Wederbrand

40.5k12 gold badges71 silver badges82 bronze badges

Collectives™ on Stack Overflow

MySQL - Using multiply LEFT JOIN statements in the same query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related