Link to home
Start Free TrialLog in
Avatar of Coco Beans
Coco Beans

asked on

Comparing two datasets

I'm looking for what SQL would give me the following:


I want to know if a student was enrolled in a college in 2016 and a different college in 2017.

I've tried:

select studentID from college where year = 2017
MINUS
select studentID from college where year = 2016


But it's not working as the student might be in 2017 but not 2016.  Any ideas? Thanks.
Avatar of Qlemo
Qlemo
Flag of Germany image

select studentID from college where year = 2017 and studentID in (select studentID from college where year = 2016)

Open in new window

Avatar of Coco Beans
Coco Beans

ASKER

That doesn't give you a list of those that are in different colleges?
Since you want to remove 2016 students, a NOT is missing from Qlemo's query:
select studentID from college where year = 2017 and studentID NOT in (select studentID from college where year = 2016)

Open in new window

Sorry if I wasn't clear.  I want to see students that were in enrolled in both 2016, 2017 but that now attend different colleges
Hi,

Do you have any column for college in your table?
ASKER CERTIFIED SOLUTION
Avatar of Qlemo
Qlemo
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
thank you
how do you know they went to a different college?

select *
from college 
where year in (2016,2017) 
and studentID NOT in (select studentID from college where DIFFERENT COLLEGE CONDITION)

Open in new window