I think You need normalize DB-tables for first.
create table Pensyarah(
IDPensyarah integer not null primary key auto_increment
, IDUser varchar(50) not null
, NamaPensyarah varchar(100) not null
, Email varchar(50) not null
, NoTel varchar(11) not null
);
create table Kursus(
IDKursus integer not null primary key auto_increment
, Semester integer not null
, KodKursus varchar(50) not null
, NamaKursus varchar(50) not null
);
create table PensyarahKursusRefs(
PensyarahKursus integer not null primary key auto_increment
, IDPensyarah integer not null
, IDKursus integer not null
, constraint IDPens_IDKurs_Sem_UQ unique key (Semester,IDPensyarah,IDKursus)
, constraint Pensyarah_FK foreign key (IDPensyarah) references Pensyarah(IDPensyarah)
, constraint Kursus_FK foreign key (IDKursus) references Kursus(IDKursus)
);Then creat view for convenient select list of "Kursus" in one column
like in sample:
create or replace view PensyarahKursus as
select
p.IDUser
, p.NamaPensyarah
, p.Email
, p.NoTel
, k.Semester
, group_concat(k.KodKursus separator ', ') kursusList
from Pensyarah p
left join PensyarahKursusRefs pkr using(IDPensyarah)
left join Kursus k using(IDKursus)
group by
p.IDUser
, p.NamaPensyarah
, p.Email
, p.NoTel
, k.Semester
;You can create procedure for convenient reference inserting:
delimiter $$
create procedure set_Kursus_ref(
in p_IDUser varchar(50)
, in p_KodKursus varchar(50)
)
begin
insert into PensyarahKursusRefs(IDPensyarah,IDKursus) values (
(select p.IDPensyarah from Pensyarah p where p.IDUser = p_IDUser)
, (select k.IDKursus from Kursus k where k.KodKursus = p_KodKursus)
);
end; $$
delimiter ;Then sample insert and select:
insert into Pensyarah(IDUser,NamaPensyarah,Email,NoTel)
values ('sample_ID_user_1','sample_Nama_1','sample.1@sapmle.com','11111111111');
insert into Pensyarah(IDUser,NamaPensyarah,Email,NoTel)
values ('sample_ID_user_2','sample_Nama_2','sample.2@sapmle.com','22222222222');
insert into Pensyarah(IDUser,NamaPensyarah,Email,NoTel)
values ('sample_ID_user_3','sample_Nama_3','sample.3@sapmle.com','33333333333');
insert into Kursus(Semester,KodKursus,NamaKursus) values (1,'KK-1','KursusNama-1');
insert into Kursus(Semester,KodKursus,NamaKursus) values …