problem with join query

I'm trying to select all the tags that go with a post in my database, but for some reason, I'm only getting one tag for every post that has tags.

This is the query I'm 开发者_运维知识库using:

SELECT p.post_id, p.post_title, t.tag_id, t.tag_name
FROM posts p
LEFT JOIN posts_tags pt ON pt.post_id = p.post_id
LEFT JOIN tags t ON pt.tag_id = t.tag_id

Any ideas?

Each post can have many tags. What I'd suggest is using MySQL's GROUP_CONCAT() and get all of the tags into one field. You can then parse that field in your script, if you need to.

edit It appears that you are left joining on the tag table, therefore it is only returning that row for the tag. Try switching around the tables in your ON clause so that it joins the other direction. You'll then need to iterate over the results (now that it should have a row per tag.) My original solution grouped all of the tags into one field.

That should work fine. In fact because its close to the SO data structure I created one using data.stackexchange.com and it works fine there