Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2004
    Posts
    3

    Unanswered: Left Join in Access

    Hi all,

    I'm having some difficulty with this left join. I have 2 tables, one with courses, one with prerequisites for those courses (the prereqs are also courses themselves)
    Courses:
    Course_ID
    Course_Name

    Prereqs:
    Course (uses Course_ID)
    Prereq (uses Course_ID)

    (this is many to many)

    I need to get all courses that are not a prerequisite for a certain course. I can do that by saying:

    SELECT DISTINCT Course_Name FROM Courses, Prereqs
    WHERE Prereqs.Prereq = Courses.Course_ID AND Prereqs.Course <> 60;


    But the problem is, there are some Courses that are not prereqs for anything, so they are not listed in the Prereqs table, so they don't show up from that query. So here's how I solved that:

    SELECT DISTINCT Course_Name FROM Courses
    LEFT JOIN Prereqs ON Courses.Course_ID = Prereqs.Prereq
    WHERE Prereqs.Course <> 60 OR Prereqs.Course IS NULL;


    That works, I'm now getting all the courses, not just ones that are prerequisites for something.

    The BIG problem is that a course can be a prerequisite for multiple courses, so for example I have a course number 13 that is a prerequisite for course 60 and 25, and this query adds course 13 to the result. But I want ONLY courses that are NOT prerequisites for 60, regardless if they are prerequisites for other courses.



    Any idea how to achieve this? I think a MINUS query would work, but as far as I know access doesn't support minus.

    Thanks in advance
    Last edited by layhna; 08-27-04 at 15:11.

  2. #2
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    I had to read your post a few times, but I think I can help. Assuming I understand what you want, you will need one query to show which course are prerequisites:

    SELECT DISTINCT Course_Name FROM Courses, Prereqs
    WHERE Prereqs.Prereq = Courses.Course_ID AND Prereqs.Course <> 60

    Then a second query that uses the results of the first query to do the join:

    SELECT DISTINCT Course_Name FROM Courses
    LEFT JOIN Prereqs ON FirstQuery.Course_ID = Prereqs.Prereq
    WHERE Prereqs.Course IS NULL;

  3. #3
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Is this what you are after:

    SELECT Courses.Course_name
    FROM Courses LEFT JOIN Prereqs ON Courses.Course_ID = Prereqs.Prereq
    WHERE Prereqs.Course<>60 or Prereqs.Prereq Is Null;

    If so then you were pretty close. If not then I've misunderstood.

    Chris

Posting Permissions

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