CODEWITHSUNDEEP
mysqlduplicatesdatabase-trigger
Bappi
Bappi

Reputation: 1

How can I avoid duplicate values in my result set?

CREATE TABLE student ( s_id int(10) NOT NULL AUTO_INCREMENT, s_roll_no int(30), s_name varchar(30), s_gender varchar(4) not null, class int(2) not null, PRIMARY KEY (s_id) );

CREATE TABLE attendance_date ( date_today varchar(10), PRIMARY KEY (date_today) );

CREATE TABLE attendance_today ( s_id int(10), s_roll_no int(30), s_name varchar(30), s_gender varchar(4), class int(2), date_today varchar(10), attendance_status varchar(2) not null default 'P' );

delimiter $$

create trigger after_insertion_into_attendance_date after insert on attendance_date for each row 

begin 

insert into attendance_today(s_id, s_roll_no, s_name, s_gender, class, date_today)
select * from student cross join attendance_date order by date_today, s_id;

end$$

delimiter ;

INSERT INTO student VALUES (1,1,'Mridul Kumar','M',1), (2,2,'Harish Paul','M',1), (3,3,'Imtiaz Hossain','M',1);

INSERT INTO attendance_date VALUES ('1st Jan'), ('2nd Jan');

now, select * from attendance_today; giving duplicates after every insertion, is there any way to avoid such duplicates inside trigger? I'm not looking for select distinct * from attendance_today; after the trigger gets activated.

Upvotes: 0

Views: 235

Answers (2)

Simeon
Simeon

Reputation: 836

This will prevent duplicate values from being inserted

insert into attendance_today(s_id, s_roll_no, s_name, s_gender, class, date_today)
select * 
from student cross join attendance_date ad
WHERE NOT EXISTS ( 
    SELECT null FROM attendance_today at
    WHERE ad.s_id = at.s_id and ad.date_today = at.date_today
)
order by date_today, s_id;

Upvotes: 1

Manash Kumar
Manash Kumar

Reputation: 1085

Try this:

begin 

insert into attendance_today(s_id, s_roll_no, s_name, s_gender, class, date_today)
select * from student cross join attendance_date group by s_id order by date_today, s_id;

end$$

Upvotes: 0

Related Questions