How to get DISTINCT row from INNER JOIN Query in SQL Server

debugcn Published at Dev

Jakir Hossain

I've been working on a school management project. A teacher has multiple subjects, I need to display only one subject in query but I get all the subjects that a teacher has. Now I need only one record for Teacher 2 (TeacherId =2 has two subjects (English & Math) from the above screenshot's query. English or Math any of them will do.

Query in Text:

SELECT        dbo.Teacher.TeacherId, dbo.Teacher.TeacherName, dbo.Gender.Gender, dbo.Teacher.DOB, ISNULL(dbo.Subject.Subject, 'No Subject') AS Subject, 
                         dbo.Teacher.Contact, dbo.Teacher.Address, dbo.Teacher.Email, dbo.Teacher.Photo
FROM            dbo.Subject INNER JOIN
                         dbo.TeacherSubject ON dbo.Subject.SubjectId = dbo.TeacherSubject.SubjectId RIGHT OUTER JOIN
                         dbo.Teacher INNER JOIN
                         dbo.Gender ON dbo.Teacher.Gender = dbo.Gender.GenderId ON dbo.TeacherSubject.TeacherId = dbo.Teacher.TeacherId

Thanks in advance for helping me a lot.

Thorsten Kettner

The easiest way would be to join just one subject per teacher in the first place. So join with an aggregate:

select
  t.teacherid, 
  t.teachername, 
  g.gender, 
  t.dob, 
  coalesce(s.subject, 'no subject') as subject, 
  t.contact, 
  t.address, 
  t.email, 
  t.photo
from teacher t
join gender g on g.genderid = t.gender
left join 
(
   select teacherid, max(subjectid) as max_subjectid
   from teachersubject
   group by teacherid
) ts on ts.teacherid = t.teacherid
left join subject s on s.subjectid = ts.max_subjectid;

Collected from the Internet

Please contact [email protected] to delete if infringement.