How to join one to many distinct in three table?

Question

I have three table I want to join

Table: Student

  ID  Fname Lname 
--------------------------------
  1    Jhon gates

Table: Registration Info

 ID   RegisterDate  StudentID_FK ClassID_FK
----------------------------------------------------
  1    2013/08/05      1             1
  2    2014/08/06      1             2
  2    2015/08/07      1             3

Table: Class

 ID   ClassName
--------------------
 1     Class1
 2     Class2
 2     Class3

Desired output

 ID  Fname  Lname  ClassName 
--------------------------------
  1   Jhon  gates    Class3

I need Student info and last class name only.

I tried with:

  Select DISTINCT [S].Fname, [S].Lname ,[C].ClassName
   FROM Registration as R 
   INNER JOIN  Student as [S] on [S].ID=[R].StudentID_FK
   INNER JOIN   Class  as[c] on [C].ID=[R].ClassID_FK

but this displays all class names

 ID  Fname  Lname  ClassName   
--------------------------------
  1   Jhon  gates    Class1  
  2   Jhon  gates    Class2  
  3   Jhon  gates    Class2

Fixed formatting and restructured the question. – ssapkota Aug 09 '15 at 15:57 — ssapkota, Aug 09 '15 at 15:57

score 0 · Answer 1 · answered Aug 07 '15 at 15:23

0

This should work:

Select s.ID,s.Fname,s.Lname,c.ClassName from student s inner join RegistrationInfo r on s.Id= r.StudentID_FK inner join Class c on c.Id=r.ClassID_FK

answered Aug 07 '15 at 15:23

Nisarg Shah

61
7

This doesn't get the last class data. – Dave C Aug 07 '15 at 15:38

Dave C · Accepted Answer · 2015-08-07T15:37:31.553

I believe this is what you're looking for. Here I'm using the ROW_NUMBER windowed function to assign a row# to each entry, partitioned by Student, ordered by date DESC -- so the most recent date is RID=1.

;WITH LastClass 
AS
(
    SELECT *, ROW_NUMBER() OVER(PARTITION BY R.StudentID_FK ORDER BY R.RegisterDate DESC) AS RID
    FROM Registration R
)

SELECT S.id, S.Fname, S.Lname, C.ClassName
FROM LastClass R
JOIN Student S ON R.StudentID_FK=S.id
JOIN Class C ON R.ClassID_FK=C.id 
WHERE RID=1

Working sample:

DECLARE @Student TABLE (id int, Fname varchar(25), Lname varchar(25))
DECLARE @RegistrationInfo TABLE (id int, RegisterDate DATETIME, StudentID_FK int, ClassID_FK int)
DECLARE @Class TABLE (id int, ClassName varchar(25))

INSERT INTO @Student (id, Fname, Lname)
SELECT 1,'Jhon','gates' UNION ALL
SELECT 2,'Bill','gates'

INSERT INTO @RegistrationInfo (id, RegisterDate, StudentID_FK, ClassID_FK)
SELECT 1,'2013/08/05',1,1 UNION ALL
SELECT 2,'2014/08/06',1,2 UNION ALL
SELECT 3,'2015/08/07',1,3 UNION ALL
SELECT 4,'2015/01/04',2,1 UNION ALL
SELECT 5,'2015/03/05',2,3

INSERT INTO @Class (id, ClassName)
SELECT 1, 'Class1' UNION ALL
SELECT 2, 'Class2' UNION ALL
SELECT 3, 'Class3'

;WITH LastClass 
AS
(
    SELECT *, ROW_NUMBER() OVER(PARTITION BY R.StudentID_FK ORDER BY R.RegisterDate DESC) AS RID
    FROM @RegistrationInfo R
)

SELECT S.id, S.Fname, S.Lname, C.ClassName
FROM LastClass R
JOIN @Student S ON R.StudentID_FK=S.id
JOIN @Class C ON R.ClassID_FK=C.id 
WHERE RID=1

score 0 · Answer 3 · answered Aug 07 '15 at 15:32

T-SQL:

SELECT 
            TOP 1
             S.Fname
            ,S.LName
            ,C.ClassName
            ,R.RegisterDate  
    FROM tblStudents AS S
    LEFT JOIN tblRegistration AS R ON S.ID = R.StudentID_FK 
    LEFT JOIN tblClasses AS C ON C.ID = R.ClassID_FK
    WHERE S.ID = 1

    ORDER BY R.RegisterDate DESC

How to join one to many distinct in three table?

3 Answers3