I have this dataset from my query
section_id user_id Role Status Entity_ID Course_Name
592253 12345 Student Active 001 PRINCIPLES OF TECHNOLOGY A
592255 12345 Student Active 001 ADVANCED PLANT & SOIL SCIENCE
593571 12345 Student Active 002 PRINCIPLES OF TECHNOLOGY A
593573 12345 Student Active 002 ADVANCED PLANT & SOIL SCIENCE
You can see I have user 12345 that have both entity_ID 001 and 002, but if you notice the course names are the same for 001 as they are for 002. 002 is the campus that the student resides on, he then goes to 001 to complete the course( cross-entity).
What I need to accomplish is in the data extract only pull the entity that the student resides on, which in this case is 002, and the course information. I do not want to pull the 001 data.
Now, this is just an example, I have students at other entities like 010, 003, 012, etc that I am in the same boat with. Additionally, I have students that are at 001, so those are good because they are not going anywhere else to do the class since they are on that home campus.
ZZ_TEST_SIS_AllCourses.Course_ID AS section_id, ZZ_TEST_SISCombinedScheduleandDemographics.StudentInternalID AS user_id, 'Student' AS Role,
'Active' AS Status, ZZ_TEST_SIS_AllCourses.Entity_ID, ZZ_TEST_SIS_AllCourses.Course_Name
FROM ZZ_TEST_SIS_AllCourses INNER JOIN
ZZ_TEST_SISCombinedScheduleandDemographics ON ZZ_TEST_SIS_AllCourses.Course_Number = ZZ_TEST_SISCombinedScheduleandDemographics.Course AND
ZZ_TEST_SIS_AllCourses.sky_Sect = ZZ_TEST_SISCombinedScheduleandDemographics.Section
WHERE (ZZ_TEST_SIS_AllCourses.Course_ID IN ('592253', '592255', '592266', '593571', '593573', '593585', '595105', '595207')) AND
(ZZ_TEST_SISCombinedScheduleandDemographics.StudentInternalID = '12345')
Does anyone have an idea on how I can accomplish this? Thank you so much