Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2003
    Location
    Columbia, SC
    Posts
    7

    Question Unanswered: 4 table query returns nothig when one is empty

    My problem is as follows: I am trying to pull data from four tables where a one table may not have data. How do I write the query that retrieves all the data from the ETS_STUDENT, ETS_ENROLLMENT, ETS_SCHOOL_DATA tables reguardless of the data in ETS_INTERNAL. ie... If the there is data in ETS_INTERNAL include it, else not. But still print the data in the other three tables.

    This is my current code.

    SELECT ETS_STUDENT.LAST_NAME, ETS_STUDENT.FIRST_NAME, ETS_STUDENT.ETS_SCHOOL, ETS_STUDENT.ETS_GRADE, ETS_SCHOOL_DATA.ETS_SCHEDULE_BLOCK, ETS_SCHOOL_DATA.ETS_SCHEDULE_TEACHER, ETS_SCHOOL_DATA.ETS_SCHEDULE_ROOM, ETS_ENROLLMENT.ETS_PROGRAM_STATUS, ETS_INTERNAL.OTHER_DESC
    FROM ((ETS_STUDENT INNER JOIN ETS_ENROLLMENT ON ETS_STUDENT.ETS_ID = ETS_ENROLLMENT.ETS_ID) INNER JOIN ETS_INTERNAL ON ETS_STUDENT.ETS_ID = ETS_INTERNAL.ETS_ID) INNER JOIN ETS_SCHOOL_DATA ON ETS_STUDENT.ETS_ID = ETS_SCHOOL_DATA.ETS_ID
    WHERE (((ETS_STUDENT.ETS_ID)=[ETS_ENROLLMENT]![ETS_ID] And (ETS_STUDENT.ETS_ID)=[ETS_SCHOOL_DATA]![ETS_ID] And (ETS_STUDENT.ETS_ID)=[ETS_INTERNAL]![ETS_ID]));

    Thank you for your time.
    Mike

  2. #2
    Join Date
    Apr 2003
    Location
    Columbia, SC
    Posts
    7
    I figured it out. I created a sub query as follows then called the sub query from the main query.

    Sub:

    SELECT ETS_STUDENT.ETS_ID, ETS_INTERNAL.OTHER_DESC
    FROM ETS_STUDENT LEFT JOIN ETS_INTERNAL ON ETS_STUDENT.ETS_ID = ETS_INTERNAL.ETS_ID;

    Main:

    SELECT ETS_STUDENT.LAST_NAME, ETS_STUDENT.FIRST_NAME, ETS_STUDENT.ETS_SCHOOL, ETS_STUDENT.ETS_GRADE, ETS_SCHOOL_DATA.ETS_SCHEDULE_BLOCK, ETS_SCHOOL_DATA.ETS_SCHEDULE_TEACHER, ETS_SCHOOL_DATA.ETS_SCHEDULE_ROOM, ETS_ENROLLMENT.ETS_PROGRAM_STATUS, [MKsPassesQuery(sub)].OTHER_DESC
    FROM ((ETS_STUDENT INNER JOIN [MKsPassesQuery(sub)] ON (ETS_STUDENT.ETS_ID = [MKsPassesQuery(sub)].ETS_ID) AND (ETS_STUDENT.ETS_ID = [MKsPassesQuery(sub)].ETS_ID)) INNER JOIN ETS_ENROLLMENT ON ETS_STUDENT.ETS_ID = ETS_ENROLLMENT.ETS_ID) INNER JOIN ETS_SCHOOL_DATA ON ETS_STUDENT.ETS_ID = ETS_SCHOOL_DATA.ETS_ID
    WHERE (((ETS_SCHOOL_DATA.ETS_ID)=[ETS_STUDENT]![ETS_ID]) AND ((ETS_ENROLLMENT.ETS_ID)=[ETS_STUDENT]![ETS_ID]));

Posting Permissions

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