Q:

How to join a mapping table in my query

I have a query that is trying to pull out all the questions in my table.
Questions
Posts
Topics
Topic Mapping
My tag tables are set up with a 3rd table for mapping the question id with the topic id.
However, how do I pull the name of the topic stored in the topic table with a JOIN statement
So basically I dont know how to do a JOIN statement on a table that has only got the topic id and not the topic name
SELECT questions.* 
     , posts.post
     , COUNT(posts.post) as total_answers
     , posts.votes
     , posts.id as post_id
     , posts.created
     , users.id as user_id
     , users.username, users.rep
     , topics.name
FROM questions
LEFT JOIN posts ON questions.id = posts.question_id
LEFT JOIN users ON questions.user_id = users.id
LEFT JOIN topics ON topic_mapping.question_id = questions.id
GROUP BY questions.id

Thanks a lot

A:

You need to join the question to the mapping table first.
SELECT questions.* 
     , posts.post
     , COUNT(posts.post) as total_answers
     , posts.votes
     , posts.id as post_id
     , posts.created
     , users.id as user_id
     , users.username, users.rep
     , topics.name
FROM questions
LEFT JOIN posts ON questions.id = posts.question_id
LEFT JOIN users ON questions.user_id = users.id
LEFT JOIN topic_mapping ON questions.id = topic_mapping.question_id
LEFT JOIN topics ON topic_mapping.topic_id = topics.id
GROUP BY questions.id

