Sql Query Help (Exists Condition)

I have a sql Query that I am trying to get working.  Problem is that it is returning all records and the where Exists based on school year is not working and I cannot understand why.

      Select  RecordID, ParentID, StudentLast, StudentFirst, StudentMiddle, DOB, Gender, Status, EmergencyContactName1, EmergencyContactPhone1, EmergencyContactRelation1, EmergencyContactName2,                           EmergencyContactPhone2, EmergencyContactRelation2, MedicalAllergies, FoodAllergies, HealthInfo, MedStatement, HealthConditions, AdminofMedication, ChildReleaseWaiver, ConsentReleaseAgreement,                           MediaRelease, ParentHelper, med2, med1, med3, dtmed1, dtmed2, dtmed3, BillingComments, MedFormDate FROM            vwStudents WHERE RecordID = RecordID AND EXISTS(Select StudentID, SchoolYear from tblclassEnrollment, tblStudents Where SchoolYear = '2015' AND tblClassEnrollment.studentID = tblStudents.RecordID) ORDER BY StudentLast Asc

Open in new window


Any help would be appreciated.  Also, in some instances I will not tack the exists on if they choose to see any students that are not enrolled in any class.
jwebster77Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kent OlsenData Warehouse Architect / DBACommented:
Hi webster,

The first issue seems to be the first part of the filter.

Select  RecordID, ParentID, StudentLast, StudentFirst, StudentMiddle, DOB, Gender, Status, 
        EmergencyContactName1, EmergencyContactPhone1, EmergencyContactRelation1, EmergencyContactName2,                           
        EmergencyContactPhone2, EmergencyContactRelation2, MedicalAllergies, FoodAllergies, HealthInfo, 
        MedStatement, HealthConditions, AdminofMedication, ChildReleaseWaiver, ConsentReleaseAgreement,                           
        MediaRelease, ParentHelper, med2, med1, med3, dtmed1, dtmed2, dtmed3, BillingComments, MedFormDate 
FROM            vwStudents 
WHERE RecordID = RecordID 
  AND EXISTS (Select StudentID, SchoolYear 
              from tblclassEnrollment, tblStudents 
              Where SchoolYear = '2015' 
                AND tblClassEnrollment.studentID = tblStudents.RecordID
             ) 
ORDER BY StudentLast Asc

Open in new window


RecordID will always be equal to RecordID.  Did you intent to join another table here?

Kent
0
jwebster77Author Commented:
No, It is there for a reason so I can construct the Where clause in code.  See below.  I know Recordid = Recordid is not the issue because if I put a lastname in it only returns that last name.  It has to be specifically in the EXISTS statement I am thinking.

 Else
            SqlString = " WHERE RecordID = RecordID"
        End If
        If Me.txtLastNameSearch.Text <> "" Then
            SqlString = SqlString + " AND studentLast LIKE '" & u.FTM(Me.txtLastNameSearch.Text) & "%'"
        End If
        If Me.cboSchoolYearEnrolledIn.Text = "2015" Then
            SqlString = SqlString + " AND EXISTS(Select StudentID, SchoolYear from tblclassEnrollment, tblStudents Where SchoolYear = '" & cboSchoolYearEnrolledIn.Text & "' AND tblClassEnrollment.studentID = tblStudents.RecordID)"
        End If

Open in new window

0
Ed DreddCommented:
OR maybe u have to use JOIN instead of EXIST

SELECT  *
FROM    vwStudents  A
JOIN    tblStudents B ON  A.RecordID = B.RecordID
JOIN  tblClassEnrollment C ON B.RecordID = C.RecordID
Where C.SchoolYear = '2015'
ORDER BY C.StudentLast Asc ;
0
PortletPaulfreelancerCommented:
SELECT
      RecordID
    , ParentID
...
    , MedFormDate
FROM vwStudents
WHERE RecordID = RecordID
AND EXISTS (
      SELECT
            StudentID
          , SchoolYear
      FROM tblclassEnrollment
         , tblStudents
      WHERE SchoolYear = '2015'
      AND tblClassEnrollment.studentID = tblStudents.RecordID
)
ORDER BY StudentLast ASC

Open in new window

line 13 (above/below), please learn to use ANSI joins, eventually you will find them easier as it separates the "join logic" from the "filtering".

Line 15 below. For an exists test, you need a "correlation" from the outer query (here this is table vwStudents) and the subquery within the exists. If this is absent then you are just asking the equivalent of:

           and exists (anything that is not null)

and, hence provided that subquery isn't NULL, ALL ROWS of the outer query satisfy that condition.

line 10 & 11 below: it isn't required for that subquery to return any data in the columns, commonly used are "select null" or "select 1" or "select *". All that is required really is the from clause of this subquery, but SQL syntax insists that a select clause exist too.

SELECT
      RecordID
    , ParentID
...
    , MedFormDate
FROM vwStudents
WHERE RecordID = RecordID
AND EXISTS (
      SELECT 
            NULL
          /* no column data is actually needed */
      FROM tblclassEnrollment
      inner join tblStudents on tblClassEnrollment.studentID = tblStudents.RecordID
      WHERE tblclassEnrollment.SchoolYear = '2015'
      AND vwStudents.RecordID = tblStudents.RecordID
)
ORDER BY StudentLast ASC

Open in new window

NOte I have assumed "vwStudents.RecordID = tblStudents.RecordID" is the correct correlation, it might not be.
0
Ed DreddCommented:
SELECT  *
FROM    vwStudents  A
INNER JOIN    tblStudents B ON  A.RecordID = B.RecordID
INNER JOIN  tblClassEnrollment C ON B.RecordID = C.RecordID
Where C.SchoolYear = '2015'
ORDER BY C.StudentLast Asc ;
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.