1

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?

Improve this question
3
  • If you want to extract all posts, with all the tags associated to the posts, the query is correct. If you think it's not - check your data. If you want to limit it to only one post, add a WHERE clause. Commented Feb 8, 2011 at 21:06
  • Your query is correct. You´re having all the tags in multiple rows for each post Commented Feb 8, 2011 at 21:22
  • you guys are right, I was expecting the rows from post to be empty when a post had multiple tags, that's why I didn't notice.. Commented Feb 8, 2011 at 21:42

2 Answers 2

Reset to default
1

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.

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

1 Comment

you seem to suggest a way to get one row from the tags. The problem seems to be that this is already the case "I'm only getting one tag for every post that has tags"
0

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

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.