Geek Answers Handbook

Group_concat with multiple joins in MySQL

Database schema

create table `questions` (
  `id` int not null auto_increment,
  `title` varchar(45) not null,
  primary key (`id`));

create table `tags` (
  `id` int not null auto_increment,
  `question_id` int not null,
  `name` varchar(45) not null,
  primary key (`id`));

create table `comments` (
  `id` int not null auto_increment,
  `question_id` int not null,
  `body` varchar(45) not null,
  primary key (`id`));

insert into questions (title) values
("title1"), ("title2"), ("title3");

insert into tags (question_id, name) values
(1, "javascript"), (1, "php"), (1, "c#"), (2, "mysql"), (2, "php"), (3, "c#");

insert into comments (question_id, body) values
(1, "comment1"), (1, "comment1"), (1, "comment2"), (3, "comment3");

The way it looks visually:

questions table

| id |  title |
|----|--------|
|  1 | title1 |
|  2 | title2 |
|  3 | title3 |

tags table

| id | question_id |       name |
|----|-------------|------------|
|  1 |           1 | javascript |
|  2 |           1 |        php |
|  3 |           1 |         c# |
|  4 |           2 |      mysql |
|  5 |           2 |        php |
|  6 |           3 |         c# |

comments table

| id | question_id |     body |
|----|-------------|----------|
|  1 |           1 | comment1 |
|  2 |           1 | comment1 |
|  3 |           1 | comment2 |
|  4 |           3 | comment3 |

Each question must have at least one tag. It may also contain 0 or more comments. There can be two comments on the same question with the same body.

Desired output

I want to select all the questions, i.e. their identifiers, headings, tags and comments.

The result should look like this:

| id |  title |       tags        |          comments          |
|----|--------|-------------------|----------------------------|
|  1 | title1 | c#,php,javascript | comment1,comment1,comment2 |
|  2 | title2 | php,mysql         | (null)                     |
|  3 | title3 | c#                | comment3                   |

Attempts to solve the problem.

I tried the following query:

select questions.id, questions.title,
  group_concat(tags.name), group_concat(comments.body)
from questions
join tags on questions.id = tags.question_id
left join comments on questions.id = comments.question_id
group by questions.id

Unfortunately, it does not work properly. He produces the following conclusion:

| id |  title | group_concat(distinct tags.name) |                                                      group_concat(comments.body) |
|----|--------|----------------------------------|----------------------------------------------------------------------------------|
|  1 | title1 |                c#,php,javascript | comment1,comment1,comment1,comment2,comment2,comment2,comment1,comment1,comment1 |
|  2 | title2 |                        php,mysql |                                                                           (null) |
|  3 | title3 |                               c# |                                                                         comment3 |

As you can see, for the first question, I get each comment three times, because there are three tags in this question.

In addition, the comments are in the wrong order. They should be in the same order in which they were inserted, that is comment1,comment1,comment2, not comment1,comment2,comment1.

distinct , .

, , , select s, , , .

SQL Fiddle

SQL Fiddle .

+4
2

GROUP_CONCAT, :

select questions.id, questions.title,
       tags.name, comments.body
from questions
join (
   select question_id, group_concat(tags.name) as name
   from tags
   group by question_id
) tags on questions.id = tags.question_id
left join (
   select question_id, group_concat(comments.body) as body
   from comments
   group by question_id
) comments on questions.id = comments.question_id
+5

, sub- . , , , , , , : -

SELECT questions.id, 
        questions.title,
        GROUP_CONCAT(tags.name ORDER BY tags.id), 
        comments.body
FROM questions
LEFT OUTER JOIN tags ON questions.id = tags.question_id
LEFT OUTER JOIN 
(
   SELECT question_id, 
            GROUP_CONCAT(comments.body ORDER BY id) as body
   FROM comments
   GROUP BY question_id
) comments ON questions.id = comments.question_id
GROUP BY questions.id, 
        questions.title,
        comments.body

, . , , , , WHERE. - , MySQL , , .

SELECT questions.id, 
        questions.title,
        GROUP_CONCAT(tags.name ORDER BY tags.id), 
        (
            SELECT GROUP_CONCAT(comments.body ORDER BY id) 
            FROM comments
            WHERE questions.id = comments.question_id
            GROUP BY question_id
        ) AS body
FROM questions
LEFT OUTER JOIN tags ON questions.id = tags.question_id
GROUP BY questions.id, 
        questions.title,
        body
+1

More articles:


All Articles