Clarification On MySQL Join Query

Question

I am having some issues creating this JOIN query.

The table setup is as follows

|      tags      ||    tag_links   ||    articles    |
|________________||________________||________________|
 |              |  |              |  |              |
 |      id      |  |  article_id  |  |      id      |
 |   tag_name   |  |    tag_id    |  |              |

There are 3 tables, and this is a many-to-many relationship.

• The objective is to find all the tags associated with a given article id.
• The tags table contains all of the tags
• The tag_links table contains the link between the articles and the tags, where the tag_id is equal to the id in the tags table, and the article_id is equal to the id in the article table
• The articles table contains the id ( amongst other columns, but the other columns are not important )

I am having a hard time because the article id is already provided. I don't think that this is even needed in the query, but I am at a loss right now. I am trying to grab all of the tag_id's that are associated with the article_id that I pass in, and then grab all of the tag_names from all of the tag_id's i just queried for.

Any help is greatly appreciated. Thanks!

Answer 1

This is a simple join you can use to get tag names for a given article id

select distinct t.* from tags t
join tag_links tl on(t.id = tl.tag_id)
where tl.article_id=@id <---- article id