Need help with MySQL JOINs dealing with four tables

Question

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.

Answer 1

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'

Answer 2

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'