troubleshooting Question

Sql Question

Avatar of mkamp81
mkamp81 asked on
Microsoft SQL Server
7 Comments1 Solution121 ViewsLast Modified:
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.
SELECT DISTINCT 
                      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
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 7 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros