CODEWITHSUNDEEP
mysql
PFranchise
PFranchise

Reputation: 6762

mysql "Where not in" using two columns

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

Upvotes: 42

Views: 40403

Answers (4)

M Silva
M Silva

Reputation: 1

the first answer pretty good, It did work for me, just was missing a ")" after PlannedTasks. I need to weed out the elements from one table, that were in the other, so...

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

This is my code:

$query_C_Ranking = sprintf("SELECT * 
                                FROM tblpinturas
                                WHERE (idCode) NOT IN 
                                        (SELECT idCode FROM tblranking)
                                ");

Upvotes: 0

Jagdeep Singh
Jagdeep Singh

Reputation: 319

@ypercubeᵀᴹ Thanks for sharing below mention query

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

My problem solved.

Upvotes: 1

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115650

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.

Upvotes: 98

jmacinnes
jmacinnes

Reputation: 1609

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.

Upvotes: 5

Related Questions