Join 3 tables and get record based on latest value only

I have 3 table as follow:

Schema (MySQL v5.7)

CREATE TABLE Student 
(
    Stud_ID varchar(255),
    Stud_Name varchar(255),
    Program varchar(255),
    Disablity_Code int
);

CREATE TABLE Disability  
(
    Disability_Code int,
    Disability_Desc varchar(255)
);

CREATE TABLE StudentSemHistory 
(
    Stud_ID varchar(255),
    Stud_Status varchar(255),
    Semester int,
    Stud_Session varchar(255)
);

INSERT INTO Student (Stud_ID, Stud_Name, Program, Disablity_Code)
VALUES
('K001', 'Tom B. Erichsen', 'AA101', '1'),
('K002', 'Maria Debuchy', 'AA101', '2'),
('K003', 'Alex Olson', 'AA101', '2'),
('K004', 'Bob Rocker', 'AA101', '3'),
('K005', 'Susan Lancaster', 'AA101', '1');

INSERT INTO Disability (Disability_Code, Disability_Desc)
VALUES 
('1', 'Normal'),
('2', 'Vision Impairment'),
('3', 'Intellectual Disability');

INSERT INTO StudentSemHistory (Stud_ID, Stud_Status, Semester, Stud_Session)
VALUES 
('K001', 'Inactive', '1', '0720'),
('K001', 'Inactive', '2', '0121'),
('K001', 'Inactive', '3', '0421'),
('K002', 'Inactive', '1', '0720'),
('K002', 'Inactive', '2', '0121'),
('K002', 'Inactive', '3', '0421'),
('K002', 'Active', '4', '0721'),
('K003', 'Inactive', '1', '0720'),
('K003', 'Inactive', '2', '0121'),
('K003', 'Inactive', '3', '0421'),
('K003', 'Active', '4', '0721'),
('K004', 'Inactive', '1', '0720'),
('K004', 'Inactive', '2', '0121'),
('K005', 'Inactive', '1', '0720'),
('K005', 'Inactive', '2', '0121'),
('K005', 'Inactive', '3', '0421'),
('K005', 'Active', '4', '0721');

Query #1:

select S.Stud_ID, S.Stud_Name, S.Program, hist.Semester, hist.Stud_Status, D.Disability_Desc
from Student S 
inner join Disability D on S.Disablity_Code = D.Disability_Code
left join (select distinct Stud_ID, Semester, Stud_Status from StudentSemHistory
           group by Stud_ID, Semester, Stud_Status) as hist
            on hist.Stud_ID = S.Stud_ID

where S.Disablity_Code <> 1;
Stud_IDStud_NameProgramSemesterStud_StatusDisability_Desc
K002Maria DebuchyAA1011InactiveVision Impairment
K002Maria DebuchyAA1012InactiveVision Impairment
K002Maria DebuchyAA1013InactiveVision Impairment
K002Maria DebuchyAA1014ActiveVision Impairment
K003Alex OlsonAA1011InactiveVision Impairment
K003Alex OlsonAA1012InactiveVision Impairment
K003Alex OlsonAA1013InactiveVision Impairment
K003Alex OlsonAA1014ActiveVision Impairment
K004Bob RockerAA1011InactiveIntellectual Disability
K004Bob RockerAA1012InactiveIntellectual Disability

View on DB Fiddle

The question is, how to make the output display the latest semester only which mean it will display 1 student per row with their latest semester.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

One way can be to join using max of Semester –

select S.Stud_ID, S.Stud_Name, S.Program, hist.Semester,D.Disability_Desc
, sem.Stud_Status
from Student S inner join Disability D on S.Disablity_Code = D.Disability_Code
left join (select distinct Stud_ID, max(Semester) as Semester from StudentSemHistory
           group by Stud_ID) as hist
            on hist.Stud_ID = S.Stud_ID
join StudentSemHistory sem on sem.Stud_ID = hist.Stud_ID
and sem.Semester = hist.Semester
where S.Disablity_Code <> 1

DBFiddle

Method 2

The problem is inside your left join. You shouldn’t put semester on group by parameter.
You have to order it by Semester first then group it by Stud_ID.

select S.Stud_ID, S.Stud_Name, S.Program, hist.Semester, hist.Stud_Status, D.Disability_Desc
from Student S
inner join Disability D on S.Disablity_Code = D.Disability_Code
left join (select distinct Stud_ID, Semester, Stud_Status from StudentSemHistory
           group by Stud_ID, Stud_Status order by Semester Desc) as hist
            on hist.Stud_ID = S.Stud_ID

where S.Disablity_Code <> 1;

or

select S.Stud_ID, S.Stud_Name, S.Program, hist.Semester, hist.Stud_Status, D.Disability_Desc
from Student S
inner join Disability D on S.Disablity_Code = D.Disability_Code
left join (select distinct Stud_ID, Semester, Stud_Status from StudentSemHistory
           group by Stud_ID, Stud_Status order by Semester Desc) as hist
            on hist.Stud_ID = S.Stud_ID

where S.Disablity_Code <> 1 GROUP BY S.Stud_ID;


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x