Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2017
    Posts
    16
    Provided Answers: 1

    Answered: LEFT JOIN returns extra results

    I am using linked tables to import data into two tables; Courses and Import. The idea is that I will start the two tables off with the same information and when it goes live (when I have all of my data) the only table that will update is the Import table. Forms allow users to update the Courses table based on the output of the CoursesRemoved and CoursesAdded queries.

    Code:
    SELECT Courses.CourseNum, Courses.CL, Courses.RQ, Courses.RG, Courses.CourseID, Courses.CourseTitle, Courses.SE, Courses.SR, Courses.SRCourseID, Courses.CLDescription, Courses.SchoolYear, Courses.SCH, Courses.RGDescription, Courses.Attribute, Courses.AttributeValue
    FROM Courses LEFT JOIN CurriculumImport ON (Courses.RGDescription = CurriculumImport.RGDescription) AND (Courses.CourseTitle = CurriculumImport.CourseTitle) AND (Courses.SCH = CurriculumImport.SCH) AND (Courses.CourseNum= CurriculumImport.CourseNum) AND (Courses.CL = CurriculumImport.CL) AND (Courses.RQ = CurriculumImport.RQ) AND (Courses.RG = CurriculumImport.RG)
    WHERE (CurriculumImport.CourseNum IS NULL
    OR CurriculumImport.CL IS NULL
    OR CurriculumImport.RQ IS NULL
    OR CurriculumImport.RG IS NULL);
    My initial thought is that my data is wrong in some places, but I emptied both tables then copied and pasted the same thing into both tables and it is still returning values. This is not in its final form either. I would like to make the LEFT JOIN for every field to track changes more completely and accurately.

    Any ideas what I could have done wrong?
    Last edited by award73; 07-13-17 at 16:17.

  2. Best Answer
    Posted by wjburke2

    "With a LEFT JOIN IF you have duplicates in the right side it will produce a duplicate in the results. I suggest you query for duplicates on your join fields. Sound like you need a key, I see CourseNum is in both sides is that not unique can there be duplicates by Year."


  3. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    614
    Provided Answers: 32
    the nature of the join can produce more records than an inner join.

  4. #3
    Join Date
    Jun 2017
    Posts
    16
    Provided Answers: 1
    The data is the exact same. I copy and pasted it from one table to another, but it still returns the same 50 records for both of the queries.

    CoursesAdded:
    Code:
    SELECT CurriculumImport.CourseNum, CurriculumImport.CL, CurriculumImport.RQ, CurriculumImport.RG, CurriculumImport.CourseID, CurriculumImport.CourseTitle, CurriculumImport.SE, CurriculumImport.SR, CurriculumImport.SRCourseID, CurriculumImport.CLDescription, CurriculumImport.SchoolYear, CurriculumImport.SCH, CurriculumImport.RGDescription, CurriculumImport.Attribute, CurriculumImport.AttributeValue
    FROM CurriculumImport LEFT JOIN Courses ON (CurriculumImport.RG = Courses.RG) AND (CurriculumImport.RQ = Courses.RQ) AND (CurriculumImport.CL = Courses.CL) AND (CurriculumImport.CourseNum= Courses.CourseNum) AND (CurriculumImport.SCH = Courses.SCH) AND (CurriculumImport.CourseTitle = Courses.CourseTitle)
    WHERE (Courses.CourseNum) IS NULL
    OR (Courses.CL) IS NULL
    OR (Courses.RQ) IS NULL
    OR (Courses.RG) IS NULL
    OR (Courses.SCH) IS NULL
    OR (Courses.CourseTitle) IS NULL
    ORDER BY Courses.RG;
    CoursesRemoved:
    Code:
    SELECT Courses.CourseNum, Courses.CL, Courses.RQ, Courses.RG, Courses.CourseID, Courses.CourseTitle, Courses.SE, Courses.SR, Courses.SRCourseID, Courses.CLDescription, Courses.SchoolYear, Courses.SCH, Courses.RGDescription, Courses.Attribute, Courses.AttributeValue
    FROM Courses LEFT JOIN CurriculumImport ON (Courses.RG = CurriculumImport.RG) AND (Courses.RQ = CurriculumImport.RQ) AND (Courses.CL = CurriculumImport.CL) AND (Courses.CourseNum = CurriculumImport.CourseNum) AND (Courses.SCH = CurriculumImport.SCH) AND (Courses.CourseTitle = CurriculumImport.CourseTitle)
    WHERE (CurriculumImport.CourseNum) IS NULL 
    OR (CurriculumImport.CL) IS NULL
    OR (CurriculumImport.RQ) IS NULL
    OR (CurriculumImport.RG) IS NULL
    OR (CurriculumImport.CourseNum) IS NULL
    OR (CurriculumImport.SCH) IS NULL
    OR (CurriculumImport.CourseTitle) IS NULL
    ORDER BY CurriculumImport.RG;
    It has got to be something about my queries. If it was the data itself wouldn't it have been caught on the import?

  5. #4
    Join Date
    Mar 2015
    Posts
    61
    Provided Answers: 4
    With a LEFT JOIN IF you have duplicates in the right side it will produce a duplicate in the results. I suggest you query for duplicates on your join fields. Sound like you need a key, I see CourseNum is in both sides is that not unique can there be duplicates by Year.

  6. #5
    Join Date
    Jun 2017
    Posts
    16
    Provided Answers: 1
    Wouldn't duplicates have been caught when I imported the data?
    I caught a bunch that way.
    For reference, I am using linked tables and copy and pasting the data into the correct tables. Managed to narrow down the errors to only 8 yesterday, but still don't know why these 8 are being retrieved by my query.

    EDIT: Some of my data had been deleted when I changed the datatypes.
    Last edited by award73; 07-24-17 at 11:12.

Posting Permissions

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