MySQL Join. Joining multiple rows from one table to another?

Question

I have a table called Articles. I also have a table for Tags. The tags table actually has 2 separate tables as its a many-to-many relationship between Articles and Tags. For example:

CREATE TABLE Articles (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
    title VARCHAR(255),
    author INT UNSIGNED NOT NULL,
    body TEXT NOT NULL -- column type may not be representative
) Engine=InnoDB;

CREATE TABLE Tags (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(32)
) Engine=InnoDB;

CREATE TABLE Article_Tags (
    article INT UNSIGNED NOT NULL,
    tag INT UNSIGNED NOT NULL,
    FOREIGN KEY (article) REFERENCES Articles (id),
    FOREIGN KEY (tag) REFERENCES Tags (id)
) Engine=InnoDB;

Now, is it possible to do a single query to return an article, and also all the tags related to that article from the Tags table in the same query?

No it's not homework. I actually posted a question on the CodeIgniter forums (I can link if you need me to) relating to a project and someone suggested doing a single query. I was asking because I wasn't sure if it's possible. — GV1
– GV1, Commented Dec 24, 2011 at 23:35
You would typically do a select * from table1 inner join table2 on table2.table1ID = table1.id inner join table3 ON table3.id = table2.table3id where table2 is your link table. — Lieven Keersmaekers
– Lieven Keersmaekers, Commented Dec 24, 2011 at 23:36
I see. I need to recap on my joins have not used them for a while. — GV1
– GV1, Commented Dec 24, 2011 at 23:37
Proper sample code (e.g. appropriate CREATE TABLE statements) is more useful than any ad-hoc table schema or english description of table columns. — outis
– outis, Commented Dec 24, 2011 at 23:44

Lieven Keersmaekers · Accepted Answer · 2011-12-24 23:44:47Z

1

As per comments, you typically use a JOIN statement to combine related records from multiple tables.

Note that I assume that

Articles has an ID column
Article_Tags has an ArticleID and TagID column
Tags has an ID column

If the actual column names differ, you should replace the corresponding columns in my statement with their actual names.

SQL Statement

SELECT *
FROM   Articles a
       INNER JOIN Article_Tags at ON at.ArticleID = a.ID
       INNER JOIN Tags t ON t.ID = at.TagID

More information about the JOIN syntax

SQL joins are used to query data from two or more tables, based on a relationship between certain columns in these tables

edited Dec 24, 2011 at 23:44

answered Dec 24, 2011 at 23:39

Lieven Keersmaekers

58.9k15 gold badges117 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Matt Ball · Accepted Answer · 2013-08-08 21:47:58Z

1

You need to use an aggregate function to combine the tags if you want to get back just a single row.

Assuming that article has (id, title, content), tag has (id, name), and article_tags (the join table) has (tag_id, article_id), then something like this ought to do it (obviously, not tested):

SELECT a.title,
       a.content,
       GROUP_CONCAT(
        DISTINCT t.name 
        ORDER BY ASC
        SEPARATOR ", "
      ) as tags
FROM article a
INNER JOIN article_tags at ON at.article_id = a.id
INNER JOIN tag t ON t.id = at.tag_id
GROUP BY a.id

edited Aug 8, 2013 at 21:47

answered Dec 24, 2011 at 23:37

Matt Ball

361k102 gold badges655 silver badges725 bronze badges

Collectives™ on Stack Overflow

MySQL Join. Joining multiple rows from one table to another?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related