Multiple values in one column MYSQL

Question

So I have three databases right now. This is the SQL code the generate each of the tables.

CREATE TABLE `Course` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(25) NOT NULL,
    `startTime` time NOT NULL,
    PRIMARY KEY (`id`)
);

CREATE TABLE `School` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `schoolname` varchar(25) NOT NULL,
    PRIMARY KEY (`id`)
);

CREATE TABLE `SchoolCourse` (
    `schoolid` int(11) NOT NULL,
    `courseid` int(11) NOT NULL,
    PRIMARY KEY (`schoolid`,`courseid`),
    KEY `FK_course` (`courseid`),
    CONSTRAINT `FK_course` FOREIGN KEY (`courseid`) REFERENCES `Course` (`id`),
    CONSTRAINT `FK_Student` FOREIGN KEY (`schoolid`) REFERENCES `School` (`id`)
);

My goal with these tables is to make it look something like this

---------------------------------------------------
|School Name  |          Courses                  |
---------------------------------------------------
| School1     | Course1, Course2, Course3         |
| School2     | Course1, Course3                  |
| School3     | Course3                           |
| School4     | Couse1, Course2, Course3, Course4 |
---------------------------------------------------

I cant seem to figure out how to do this. I have tried inputting the values like this.

INSERT INTO 'ShcoolCourse' VALUES (
(1, 1), (1, 2), (1, 3),
(2, 1), (2, 3),
(3, 3),
(4, 1), (4, 2), (4, 3), (4, 4));

Then from there I would perform a join and try to put the values into one column but no luck so far, I have looked around at other question and some of them have the same idea but not exactly what I am doing. Any help would be greatly Appreciated.

@nobalG What would be a good work around of this then? If i was to not do it this way? — cunniemm, Sep 25 '17 at 03:15
@nobalG it's already normalized enough, the real question here is about how to get that desired result (`SchoolCourse` grouped by `School`) — Bejasc, Sep 25 '17 at 03:21
@nobalG no where in this design is there more than a single value stored in a column. — Bejasc, Sep 25 '17 at 03:22
@nobalG My question might have come across a little wrong both answers I received were right, I just want to be able to call a SQL query and be able to see the results of all the courses in the school. I don't want a table that has more then one value in each field. — cunniemm, Sep 25 '17 at 03:24

Tim Biegeleisen · Accepted Answer · 2017-09-25T03:20:46.893

2

Your current schema is normalized and perfectly fine. If you want such comma separated output, then just generate it at the time you query using GROUP_CONCAT:

SELECT
    t1.id,
    t1.schoolname AS `School Name`,
    GROUP_CONCAT(t3.name) AS Courses
FROM School t1
LEFT JOIN SchoolCourse t2
    ON t1.id = t2.schoolid
LEFT JOIN Course t3
    ON t2.courseid = t3.id
GROUP BY
    t1.id,
    t1.schoolname

Note that I include both the id and schoolname in the aggregation because it is possible that two different schools might coincidentally have the same name, and we need some way to distinguish them.

edited Sep 25 '17 at 03:20

answered Sep 25 '17 at 03:13

Tim Biegeleisen

502,043
27
286
360

Just out of curiousity, I want to confirm is it the right way to store data? It may appear that user may be only storing and retrieving the data, but in future this will complicate things as the table is not normalized. – nobalG Sep 25 '17 at 03:53
My answer has nothing to do with storing data. Your table setup looks good, is normalized, and is the way to go. Don't store CSV data, just build what you need when you query. – Tim Biegeleisen Sep 25 '17 at 03:55

score 1 · Answer 2 · answered Sep 25 '17 at 03:14

1

You can use GROUP BY with group_concat:

select
    s.schoolname `School Name`,
    group_concat(c.name) Courses
from school s
left join schoolcourse sc on s.id = sc.schoolid
left join cource c on c.id = sc.courseid
group by s.id

answered Sep 25 '17 at 03:14

Blank

12,308
1
14
32

Thank you for your answer. This worked great just like the other answer. – cunniemm Sep 25 '17 at 03:26

Multiple values in one column MYSQL

2 Answers2