Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2003
    Location
    Columbia, SC
    Posts
    2

    Question Unanswered: Determining keys not in table

    I am trying to write a query to determine what keys in one table are not
    present in another table. An example as follows

    SELECT StudentTable.StudentID, EnrollmentTable.StudentID,
    EnrollmentTable.ProgramStatus, ServicesTable.StudentID
    FROM StudentTable, EnrollmentTable, ServicesTable
    WHERE StudentTable.StudentID = Not(StudentTable.StudentID =
    ServicesTable.StudentID)

    My query should give me all students that have an active program status and
    have NOT recieved services. ie, they have no entry in ServicesTable.

  2. #2
    Join Date
    Nov 2003
    Posts
    267
    SELECT StudentTable.StudentID, EnrollmentTable.StudentID, EnrollmentTable.ProgramStatus, ServicesTable.StudentID
    FROM (StudentTable INNER JOIN EnrollmentTable ON StudentTable.StudentID = EnrollmentTable.StudentID) LEFT JOIN ServicesTable ON StudentTable.StudentID = ServicesTable.StudentID
    WHERE (((ServicesTable.StudentID) Is Null));

    S-

  3. #3
    Join Date
    Dec 2003
    Location
    WA, USA
    Posts
    51

    Re: Determining keys not in table

    Try somthing like

    SELECT StudentTable.StudentID, EnrollmentTable.StudentID,
    EnrollmentTable.ProgramStatus, ServicesTable.StudentID
    FROM EnrollmentTable INNER JOIN (ServicesTable RIGHT JOIN StudentTable ON ServicesTable.StudentID = StudentTable.StudentID ) ON EnrollmentTable.StudentID = StudentTable.StudentID
    WHERE ServicesTable.StudentID is null

    I think I got the syntax straight.

    Basiclly you need to create a right join by changing the join properties between student and services to "Show all from Student..." and leave a straight join between student and enrollment. then set the criteria for ServicesTable.StudentID = to "is null".

    I hope this helps
    Bob

  4. #4
    Join Date
    Dec 2003
    Location
    Columbia, SC
    Posts
    2
    Thank you for the aid. S- was used and worked as anticipated. I haven't tried yours Bob, but it would no doubt have worked also. Thanks again.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •