469,934 Members | 1,847 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,934 developers. It's quick & easy.

Want the ones "left over" after an inner join

I thought the following would work, but I thought wrong:
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT AvailableCourses.*
  2. FROM AvailableCourses INNER JOIN PlannedCourses ON PlannedCourses.CourseID<>AvailableCourses.CourseID
  3. WHERE  PlannedCourses.EmplID=?;
I want to display courses that are available for an employee to take, but I do not want to display courses that are already planned.

If I change <> to "=" then i get the courses that are planned. I want the ones left over.

thanks for looking
Mar 4 '09 #1
2 1371
solved: Discovered "NOT IN" clause
Expand|Select|Wrap|Line Numbers
  1. SELECT AvailableCourses.*
  2. FROM AvailableCourses
  3. WHERE AvailableCourses.CourseID NOT IN (SELECT PlannedCourses.CourseID FROM PlannedCourses WHERE EmplID=?);
Mar 5 '09 #2
32,233 Expert Mod 16PB
A more standard (and likely more efficient) way of doing it would be :
Expand|Select|Wrap|Line Numbers
  2. FROM AvailableCourses AS AC LEFT JOIN PlannedCourses AS PC
  3.   ON AC.CourseID=PC.CourseID
  4. WHERE PC.CourseID Is Null
  5.   AND PC.EmplID=?
Apr 4 '09 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by Petre Agenbag | last post: by
reply views Thread by Petre Agenbag | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.