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.
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.
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)
ASKER
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?
Do you have any column for college in your table?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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