How Do I Join Data Points From The Same Table?-CodePudding

So I am new to MySQL. I created a database where it's my book collection. It has title, genre, volume, and book type (manga, light novel, regular novel, etc.). Here's my code so far for creating the database and adding data.

create database books;
use books;
create table books_in_my_possession (title varchar(100), genre varchar(100), volume varchar (100));
insert into books_in_my_possession (title, genre, volume) values ('Assassins Creed Blade of Shao Jun', 'Historical Fiction', '1');
insert into books_in_my_possession (title, genre, volume) values ('Assassins Creed Blade of Shao Jun', 'Historical Fiction', '2');
insert into books_in_my_possession (title, genre,volume) values ('Torture Princess: Fremd Torturchen', 'Isekai', '1');
insert into books_in_my_possession (title, genre, volume) values ('Torture Princess: Fremd Torturchen', 'Dark Fantasy', '1');
alter table books_in_my_possession
add column book_type varchar(100);
update books_in_my_possession
set book_type = 'manga' 
where title = 'Assassins Creed Blade of Shao Jun';
update books_in_my_possession
set book_type = 'light novel'
where title = 'Torture Princess: Fremd Torturchen';

What I want to do is combine 'Torture Princess: Fremd Torturchen' and '1' (same title) by genres so it has both 'Isekai' and 'Dark Fantasy'. I could have written 'Isekai/Dark Fantasy' or 'Isekai, Dark Fantasy' in one of the value lines but I want them to count as separate as books have multiple genres.

How would I go about doing this?

CodePudding user response：

I think you are trying to use group_concat:https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html#function_group-concat

select  title,
        GROUP_CONCAT (genre) as genre,
        volume,
        book_type 
from books.books_in_my_possession
group by title,volume,book_type ;

Demo: https://www.db-fiddle.com/f/7yUJcuMJPncBBnrExKbzYz/121