sixfeet sixfeet - 1 year ago 79
MySQL Question

MySQL statement to return unique association

I have the following table:

idStudent class day hour
1 Biology Monday 10:00 AM
1 Biology Friday 11:00 AM
1 Chemistry Monday 09:00 AM
2 Biology Monday 10:00 AM
2 Chemistry Friday 09:00 AM
1 Informatics Wednesday 11:00 AM

I want to get the following output:

idStudent class
1 Biology
1 Chemistry
2 Biology
2 Chemistry
1 Informatics

What select statement should I use to get that result(only one association between an idStudent and a class)?
For example, idStudent 1 has biology class monday and friday, and I want to obtain only the association between an idStudent and a class, idStudent 1 and class biology.
I've tried using distinct or group by, but in that case I receive only one result for idStudent using distinct or only the class using group by.

Answer Source

Use select distinct

select distinct idstudent, class
from my_table;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download