Mysql getting many-to-many relationship data in a row

Question

Hi I have seemingly simple problem which is causing my head to hurt.

I have three tables in a mysql db which describe a many-to-many relationship between article title and several (variable number) keywords.

Article:

+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int(11)      | NO   | PRI | NULL    | auto_increment |
| volume_id  | varchar(11)  | NO   | MUL | NULL    |                |
| title      | longtext     | NO   |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+

Keywords:

+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| id      | int(11)      | NO   | PRI | NULL    | auto_increment |
| keyword | varchar(355) | NO   |     | NULL    |                |
+---------+--------------+------+-----+---------+----------------+

Article_keywords (through table):

+------------+---------+------+-----+---------+----------------+
| Field      | Type    | Null | Key | Default | Extra          |
+------------+---------+------+-----+---------+----------------+
| id         | int(11) | NO   | PRI | NULL    | auto_increment |
| article_id | int(11) | NO   | MUL | NULL    |                |
| keyword_id | int(11) | NO   | MUL | NULL    |                |
+------------+---------+------+-----+---------+----------------+

What I want is a single table where one row includes the title and its associated keywords, so that it can be edited by someone who is not a programmer. Of course I will also need to do the reverse to update the fields, but I think that is much more straightforward.

Is this even possible in MySQL or will have to use a scripting language to accomplish this?

Any help much appreciated.

You'll need to use a subquery for this - if I wasn't at work and had more time I'd post it, but I'm sure someone else would be me to it anyway. — Brian Driscoll, Jan 31 '12 at 15:45
what is happening today? http://stackoverflow.com/questions/9082139/how-to-use-in-clause-in-subquery#comment11402970_9082139 — dani herrera, Jan 31 '12 at 16:03

score 1 · Accepted Answer · answered Jan 31 '12 at 15:52

SELECT a.ID, a.title, c.keyword
FROM    `Article` a INNER JOIN `Article_keywords` b
            ON a.id = b.article_id
        INNER JOIN `Keywords` c 
            ON b.keyword_ID = c.id

OR if you want to have a single row for every title

SELECT a.ID, a.title, GROUP_CONCAT(c.keyword) `keyword`
FROM    `Article` a INNER JOIN `Article_keywords` b
            ON a.id = b.article_id
        INNER JOIN `Keywords` c 
            ON b.keyword_ID = c.id
GROUP BY a.ID

score 0 · Answer 2 · answered Jan 31 '12 at 15:48

Is this even possible in MySQL or will have to use a scripting language to accomplish this?

You didn't think that a non-programmer can edit data directly in mysql console or phpmyadmin, did you?

Obviously, you need to create a page for this (in PHP, for example).

SQL to select a page with all its keywords is pretty trivial. I'm sure you know it. So, go get a book on PHP and start building that page.

score 0 · Answer 3 · answered Jan 31 '12 at 15:58

0

select article.id, article.title,
group_concat(keyword.keyword) keywords
from article inner join article_keyword on article_id=article.id
inner join keyword on keyword_id=keyword.id
group by article.id,article.title

answered Jan 31 '12 at 15:58

mdprotacio

842
6
18

Mysql getting many-to-many relationship data in a row

3 Answers3