CODEWITHSUNDEEP
sqlt-sql
user911534
user911534

Reputation: 25

Combine 2 separate result sets using SQL

I have two separate result sets I want to combine in a one final result

(I)

SELECT * FROM
    (       
    SELECT T_Student_History.StudenID,
    Convert(varchar(11), MAX(T_Student_History.Created),101)AS 'Student Last entry',
    'History' AS 'Subject Name' 
    FROM T_Student_History 
    Group BY T_Student_History.StudentID
    UNION ALL

    SELECT T_Student_geography.StudentID,
    Convert(varchar(11), MAX(T_Student_geography.Created),101)AS 'Student Last entry',
    'Geography' AS 'Subject Name' 
    FROM T_Student_geography
    Group BY T_Student_geography.StudentID
    Union ALL

    SELECT T_Student_Computing.StudentID,
    Convert(varchar(11), MAX(T_Student_Computing.Created),101)AS 'Student Last entry',
    'Computing' AS 'Subject Name' 
    FROM  T_Student_Computing
    Group BY  T_Student_Computing.StudentID
) AS T_AllSubjects

(II)

 SELECT  T_Students.Forename+' '+T_Students.Surname AS 'Student Description',
    T_Students.Email,
    T_Students.Mobile,

    FROM T_Students

I want a result like:

forename  surname  mobile  StudentID  Student Last Entry  Subject Name
--------  -------  ------  ---------  ------------------  ------------

Upvotes: 0

Views: 806

Answers (2)

StevieG
StevieG

Reputation: 8729

You should be able to INNER JOIN them like this:

SELECT  
  T_Students.StudentID
  T_Students.Forename+' '+T_Students.Surname AS 'Student Description',                  
  T_Students.Email,
  T_Students.Mobile,
  T_AllSubjects.StudentLastEntry 
  T_AllSubjects.Subject Name
FROM T_Students 
INNER JOIN (SELECT * 
            FROM
            (        
             SELECT 
               T_Student_History.StudentID,
               Convert(varchar(11), MAX(T_Student_History.Created),101)AS StudentLastEntry,
               'History' AS SubjectName
             FROM T_Student_History 
             GROUP BY T_Student_History.StudentID
             UNION ALL
             SELECT 
               T_Student_geography.StudentID,
               Convert(varchar(11), MAX(T_Student_geography.Created),101)AS StudentLastEntry,
               'Geography' AS SubjectName
             FROM T_Student_geography
             GROUP BY T_Student_geography.StudentID
             UNION ALL
             SELECT T_Student_Computing.StudentID,
               Convert(varchar(11), MAX(T_Student_Computing.Created),101)AS StudentLastEntry,
               'Computing' AS SubjectName
             FROM  T_Student_Computing
             GROUP BY T_Student_Computing.StudentID
            ) 
           ) T_AllSubjects 
ON T_Students.Studentid = T_AllSubjects.StudentID

Upvotes: 1

Purplegoldfish
Purplegoldfish

Reputation: 5294

If im understanding you correctly I think you want to use WITH here so something like:

WITH T_Subjects AS
( 
SELECT T_Student_History.StudenID, 
    Convert(varchar(11), MAX(T_Student_History.Created),101)AS 'Student Last entry', 
    'History' AS 'Subject Name'  
    FROM T_Student_History  
    Group BY T_Student_History.StudentID 
    UNION ALL 

    SELECT T_Student_geography.StudentID, 
    Convert(varchar(11), MAX(T_Student_geography.Created),101)AS 'Student Last entry', 
    'Geography' AS 'Subject Name'  
    FROM T_Student_geography 
    Group BY T_Student_geography.StudentID 
    Union ALL 

    SELECT T_Student_Computing.StudentID, 
    Convert(varchar(11), MAX(T_Student_Computing.Created),101)AS 'Student Last entry', 
    'Computing' AS 'Subject Name'  
    FROM  T_Student_Computing 
    Group BY  T_Student_Computing.StudentID 
)

Then just do:

  SELECT  
  T_Students.StudentID
  T_Students.Forename+' '+T_Students.Surname AS 'Student Description',                  
  T_Students.Email,
  T_Students.Mobile,
  T_Subjects.StudentLastEntry 
  T_Subjects.Subject Name
FROM T_Students 
LEFT OUTER JOIN T_Subjects ON T_Students.StudentID = T_Subjects.StudentID

Upvotes: 2

Related Questions