Comparing variables from a select statement

Transact-SQL
1

I apologize in advance if what I'm looking for is as clear as mud. :slight_smile:

I've got two select statements right now. We'll call them A and B. They're below:

A:
> >select max(award_year_token), student_ssn, ul.value_06 AS [newest_esign] from user_long ul
> >join student s on s.student_token=ul.student_token
> >group by student_number, ul.value_06

B:
> > select min(award_year_token), student_ssn, ul.value_06 AS [oldest_esign] from user_long ul
> > join student s on s.student_token=ul.student_token
> > group by student_number, ul.value_06

I'd like to know for which "student_number" that value_06 does not match.

2

Since I'm not 100% sure which table column "award_year_token" is in, I just have to join the given queries as they are, I can't try to adjust them for maximize efficiency.

select a.*, b.*
from (
    select max(award_year_token), student_ssn, ul.value_06 AS [newest_esign] 
    from user_long ul
    join student s on s.student_token=ul.student_token
    group by student_number, ul.value_06
) as a
inner join (
    select min(award_year_token), student_ssn, ul.value_06 AS [newest_esign] 
    from user_long ul
    join student s on s.student_token=ul.student_token
    group by student_number, ul.value_06
) as b on b.student_number = a.student_number and b.newest_esign <> a.newest_esign