How can I append records that don't already exist in a table from a cartesian product query?

Question

My current query returns every possible outcome from 2 tables and inserts them into a junction table.

My current query looks like this:

INSERT INTO tblJunction_Courses_Software ( CourseID, SoftwareID )
SELECT tblCourses.CourseID, tblSoftware.SoftwareID
FROM tblSoftware, tblCourses
WHERE (((tblSoftware.Exclude)=No));

How can I add an additional clause to make sure that duplicate data is not appended every time the query is run?

Answer 1

I think you can do what yo want with a not exists clause:

INSERT INTO tblJunction_Courses_Software ( CourseID, SoftwareID )
     SELECT c.CourseID, s.SoftwareID
     FROM tblSoftware as s CROSS JOIN tblCourses as c
     WHERE s.Exclude = No AND
           NOT EXISTS (SELECT 1
                       FROM tblJunction_Courses_Software as cs
                       WHERE cs.CourseId = c.CourseId AND
                             cs.SoftwareId = s.SOftwareId
                      );