[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Sql Question

Posted on 2016-09-12
7
Medium Priority
?
60 Views
Last Modified: 2016-09-12
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')

Open in new window


Does anyone have an idea on how I can accomplish this?  Thank you so much
0
Comment
Question by:mkamp81
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 44

Expert Comment

by:zephyr_hex (Megan)
ID: 41794684
If students are at other entities, how do we know which one you want?  Or is there only ever one other entity possible, and in that case, you want the one that isn't 001 ?
0
 
LVL 8

Expert Comment

by:Randy Peterson
ID: 41794692
This can be done if you can tell me how to identify the campus the student resides on.  I guess the question is, if the student doesn't reside on the home campus (001)  will their campus always have a duplicate entry for 001?
0
 
LVL 10

Expert Comment

by:Duy Pham
ID: 41794702
I don't really understand what you are trying to accomplish. If you don't want 001 data, why don't you just filter it out (by adding another condition into WHERE clause like AND ZZ_TEST_SIS_AllCourses.Entity_ID <> '001')?

Or you are trying to get the courses that student resides on and also completes those courses at home campus (001)? If that is the case, you can add another contidion into WHERE clause to only get the courses that student completes at home campus like AND ZZ_TEST_SIS_AllCourses.Course_Name IN (SELECT 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') AND (ZZ_TEST_SIS_AllCourses.Entity_ID = '001'))

Code sample could look like:
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') AND

-- if filtering out only 001 - home campus
                      ( ZZ_TEST_SIS_AllCourses.Entity_ID <> '001) AND

-- if we need to make sure the courses also be completed by the student at home campus
                      (ZZ_TEST_SIS_AllCourses.Course_Name IN (
                              SELECT 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') AND (ZZ_TEST_SIS_AllCourses.Entity_ID = '001')))

Open in new window

0
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 

Author Comment

by:mkamp81
ID: 41794865
There is a field called School number I brought in. That is how I know which school the student is from.  
The ones highlighted in the yellow are the ones I need. Where the school number and the away school match. So if there is a 002 as the school number and away school has 002 that is what I need.

2016-09-12_13-40-02.png
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.Course_Name, ZZ_TEST_SISCombinedScheduleandDemographics.SchoolNumber, 
                      ZZ_TEST_SIS_AllCourses.Entity_ID as 'Away School'
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_SISCombinedScheduleandDemographics.StudentInternalID = '12346')

Open in new window

0
 
LVL 44

Accepted Solution

by:
zephyr_hex (Megan) earned 2000 total points
ID: 41794884
Then just look for the condition where SchoolNumber = [Away School]
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.Course_Name, ZZ_TEST_SISCombinedScheduleandDemographics.SchoolNumber, 
                      ZZ_TEST_SIS_AllCourses.Entity_ID as 'Away School'
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_SISCombinedScheduleandDemographics.StudentInternalID = '12346')
AND  (ZZ_TEST_SIS_AllCourses.Entity_ID = ZZ_TEST_SISCombinedScheduleandDemographics.SchoolNumber)

Open in new window

0
 

Author Closing Comment

by:mkamp81
ID: 41794941
Thank you, I had figured that out before I saw that you posted this. I guess I just needed to get ideas then that got me seeing what I was doing incorrectly. Again thank you
0
 
LVL 44

Expert Comment

by:zephyr_hex (Megan)
ID: 41794951
Glad you figured it out.  Sometimes I find I end up solving my own problem just in the process of thinking through how I would explain the problem when posting an EE question.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question