I need help with a MySQL开发者_运维技巧 query. This example is much simpler than the one I actually need, but it's similar. Here are my example tables, I tried to make it easy to read.
posts
---------------
id date
1 2010
2 2010
posts_tags
---------------
post_id tag_id
1 1
1 2
1 3
tags
--------------
id tagname
1 foo
2 bar
3 hello
tags_active
--------------
tag_id active
1 yes
2 no
3 no
Basically what I need to do is return the active tags belonging to post id 1. In this case, it should return foo
Here was my attempt at such a query:
SELECT tags.tagname
FROM posts
JOIN posts_tags ON posts.id = posts_tags.post_id
JOIN tags ON posts_tags.tag_id = tags.id
JOIN tags_active ON tags.id = tags_active.id
WHERE tags_active.active = 'yes'
Unfortunately, doesn't seem to work. Please advise, thanks for your time.
There's a slight error in your last join - and it's probably worth explicitly INNER joining in this case.
SELECT
tags.tagname
FROM
posts
INNER JOIN
posts_tags ON posts.id = posts_tags.post_id
INNER JOIN
tags ON posts_tags.tag_id = tags.id
INNER JOIN
tags_active ON tags.id = tags_active.tag_id
WHERE
tags_active.active = 'yes'
Your last join is on tags.id = tags_active.id
.
It should be tags.id = tags_active.tag_id
:
SELECT tags.tagname
FROM posts
JOIN posts_tags ON posts.id = posts_tags.post_id
JOIN tags ON posts_tags.tag_id = tags.id
JOIN tags_active ON tags.id = tags_active.tag_id
WHERE tags_active.active = 'yes'
精彩评论