I have two tables, Semester1 and Semester2. Semester1:

StudentId	SubjectId
abc	sub1
def	sub1
ghi	sub1

Semester2:

StudentId	SubjectId
abc	changedSub1
def	sub1
ghi	changedSub2
newStudent1	sub2
newStudent2	sub3

I am trying to write a single Select statement such that it selects rows from Semester2 that have:

1. New StudentIds - i.e., StudentIds in Semester2 that are not in Semester1. So the result from this requirement should be Semester2's newStudent1 and newStudent2 rows.

AND

2. Changed SubjectIds - i.e., SubjectId are different for the same StudentId between Semester1 and Semester2. So the result from this requirement should be Semester2's changedSub1 and changedSub2 rows.

I have been able to write two separate queries to select the 2 requirements separately:

-- Part 1
SELECT * FROM Semester2
    WHERE StudentId NOT IN ( SELECT StudentId from Semester1 );

-- Part 2
SELECT Semester2.StudentId, Semester2.SubjectId 
FROM   Semester2
JOIN Semester1
  ON (Semester1.StudentId = Semester2.StudentId)
  WHERE Semester1.SubjectId <> Semester2.SubjectId;

How can I combine the two queries? Or if there is a better/easier/clearer way to write both requirements as a single query (without combining my above queries), how do I do that?

CodePudding user response：

You could also do it in a single query using a join if UNION doesn't count for "single query":

SELECT s2.* 
  FROM Semester2 s2
       LEFT OUTER JOIN Semester1 s1
         ON s2.StudentId = s1.StudentId
         AND s2.SubjectId = s1.SubjectId
 WHERE s1.StudentId IS NULL;

The WHERE clause will make it so only results where there isn't a perfect match in Semester1 appear.

CodePudding user response：

It looks like a single query with an outer join should suffice

select s2.*
from semester2 s2
left join semester1 s1 on s1.studentId = s2.studentId
where s1.studentId is null or s2.SubjectId != s1.SubjectId;

CodePudding user response：

You might just need to extend your "Part 1" query a little. Right now it excludes all students from semester 1, but you only want to exclude students from semester 1 that do not have changed subjects in semester 2.

Something like this:

SELECT * FROM Semester2
WHERE StudentId NOT IN (-- Student ids with same subject from semester 1
                        SELECT StudentId FROM Semester1
                        WHERE Semester1.SubjectId = Semester2.SubjectId);

But I haven't tested it. Please let me know if I made some terrible mistake.