How to implement one-to-many query by MySQL

This article is about how MySQL implements one-to-many queries. The editor thought it was very practical, so I shared it with you as a reference. Let's follow the editor and have a look.

This time you are going to implement an one-to-many query using MySQL's group_concat function.

Group_concat

To put it simply, the function of this function is to connect multiple fields. The detailed explanation of the function can be seen in this article.

Data sheet

First, let's set up two tables.

CREATE TABLE `student` (`id` int (11) NOT NULL AUTO_INCREMENT, `name` char (10) NOT NULL, PRIMARY KEY (`id`)) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;-- Records of student-- INSERT INTO `student` VALUES ('1th,' tom') INSERT INTO `student` VALUES ('2years,' jerry'); CREATE TABLE `student` (`id` int (11) NOT NULL AUTO_INCREMENT, `sid` int (11) NOT NULL, `cname` char (10) NOT NULL, PRIMARY KEY (`id`) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 -Records of course-- INSERT INTO `kids' VALUES ('1th,' 1', 'language'); INSERT INTO''VALUES' ('2','1', 'math'); INSERT INTO''VALUES' ('3','2', 'English') INSERT INTO `art 'VALUES (' 4pm, '2pm,' sports'); INSERT INTO `fitness' VALUES ('5pm,' 2pm, 'art'); examples

If we use the usual SQL query

SELECT s.`name`, c.`c _ name` FROM student AS s LEFT JOIN course AS c ON c.s_id = s.id

The result of the query is

Let's use the group_concat function to query

SELECT s.`name`, (SELECT group_concat (course.c_name) FROM course WHERE course.s_id = s.id) FROM student AS s; Thank you for reading! About MySQL how to achieve one-to-many query to share here, I hope the above content can be of some help to you, so that you can learn more knowledge. If you think the article is good, you can share it and let more people see it.