Complicated Sub-query - is this possible?

Question

I've got 2 tables: one stores tags, the other stores articles. There's a mode "Get articles by tag", which basically takes all articles, tagged "x". In my articles table I use a filed, called Tags, that stores data in such pattern 'tag1, tag2, tag3, ...'.

So I want to get everything work by just a single query like that:

SELECT *, 
       (SELECT tagname 
          FROM `tags_table` 
         WHERE tagurn LIKE 'x') as TAGNAME 
  FROM `articles_table` 
 WHERE (Tags LIKE 'TAGNAME,%' OR Tags LIKE '%, TAGNAME' ... and so on)

I don't know if it's even possible, but I'd really like to use a single query (with a sub-query) instead of two different.

Answer 1

This is the wrong way to store a many-to-many relationship in a database.

You should have a schema like:

     articles: [PK] article_id, ... (should have no reference to tags)
         tags: [PK] tag_id, tag_name, ...
articles_tags: [FK] article_id, [FK] tag_id

[PK] = primary key, [FK] = foreign key

Where articles_tags is a junction table. Now, you can get all articles with a given tag with (if you know the tag_id you won't even need the JOIN):

    SELECT article_id, ...
      FROM articles_tags
INNER JOIN tags ON tags.tag_id = articles_tags.tag_id
     WHERE tag_name = 'TAGNAME'