mysql "Where not in" using two columns

Question

I have one temporary table that contains userID and taskID. It is called CompletedTasks.
I have a second table that contains userID and taskID. It is called PlannedTasks.

I need to get a list of all taskIDs that were completed, but not planned.
So, I need to somehow weed out from completed tasks, all rows where both:

PlannedTasks.userID != CompletedTasks.userID  

AND

PlannedTasks.taskID != CompletedTasks.taskID 

Answer 1

You can use this (more compact syntax):

SELECT * FROM CompletedTasks WHERE (userID, taskID) NOT IN       ( SELECT userID, taskID         FROM PlannedTasks       ) ; 

or the NOT EXISTS version (which although more complex, should be more efficient with proper indexes):

SELECT c.* FROM CompletedTasks AS c WHERE NOT EXISTS        ( SELECT 1         FROM PlannedTasks AS p         WHERE p.userID = c.userID           AND p.taskID = c.taskID       ) ; 

and of course the LEFT JOIN / IS NULL version that @jmacinnes has in his answer.

Answer 2

Is this what you need?

select ct.* from completedTasks ct left outer join plannedTasks pt on ct.taskId = pt.TaskId and ct.userId = pt.userId where pt.taskId is null 

However, I agree with the comment - given what we know from the question a status column sounds like a better schema than two tables.