issue with distinct in joint table

Question

I don't understand why it's would still display duplicate students id eventhough I 'm using DISTINCT :

SELECT DISTINCT students.id, students.firstname, students.lastname, teachers.lastname as teachers
FROM students, teachers
ORDER BY students.ID;

Answer 1

Also I think you shouldn't use cross join. If tables teachers and students contain common columns you could use inner or left join.

Answer 2

Kevin Nadjarian

If you use distinct with multiple columns then it will apply to all columns.

You need to use INNER JOIN here.

SELECT s.id, s.firstname, s.lastName, t.lastName AS "teacher" FROM students s INNER JOIN teachers t ON s.teacherid = t.id ORDER BY s.id

Answer 3

Because rows with the same students.id contain different teachers.lastname. Distinct keyword is applied to all columns.