CODEWITHSUNDEEP
mysqlsql
cunniemm
cunniemm

Reputation: 689

Multiple values in one column MYSQL

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.

Upvotes: 0

Views: 159

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520958

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.

Upvotes: 2

Blank
Blank

Reputation: 12378

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

Upvotes: 1

Related Questions