CODEWITHSUNDEEP
mysql
user2516251
user2516251

Reputation: 15

how to delete duplicate records from a table

this is student table the student have two subjects maths and reading

the two subject are registered with same id i want to display only the maths students here the condtion is primary key(sid,subject); 

sid    fname   lname    subject  phno
---    -----   -----    -------- -----
1      vvk     v        math     4444
1      vvk     v        read     4444
2      hari    h        math     5555
2      hari    h        read     5555

3      kalyan  k        read     6666

How can I display a single record from the student has take subject math or read, like this:

1      vvk     v        math     4444 
2      hari    h        math     5555
3      kalyan  k        math     6666

here sid and subject are composite primary keys if please write query to above result in mysql

Upvotes: 0

Views: 108

Answers (1)

Patrick Evans
Patrick Evans

Reputation: 42736

Like i mention in my comment you should normalize your tables, your current structure is inefficent.

Until then use group along with group_concat it will group together the subjects into one field 

SELECT 
    sid,fname,lname,phno,group_concat(subject) as subjects 
FROM 
    tablename 
GROUP BY 
    sid

Will produce something like 

3  kalyan  k  6666  math,read

Upvotes: 2

Related Questions