CODEWITHSUNDEEP
sqlsql-servert-sql
thegunner
thegunner

Reputation: 7163

sql select where not in

I'm trying to select some data from a task table, but not any subtasks which a user may have created for themselves. So, I want to filter out any tasks that have a parent_taskid which is a task_id already assigned to that user.

E.g.

UserID | Parent_TaskID | TaskID
------ | ------------- | ------
435    | 149329        | 161280  
435    | 149330        | 210717  
435    | 149330        | 228100  
435    | 156991        | 149330  
169    | 161280        | 546540  
169    | 456842        | 458764

So from the table above TaskIDs 210717 & 228100 would be removed from my select because their parent (149330) is a taskID already assigned to that user - making them subtasks. - but 546540 would not be removed because it is a taskID assigned to another user.

So I'm thinking something like

select Task.taskID, Task.Parent_taskID, Task.userID 
from task
where Task.Parent_TaskID not in (??? select taskID from task where ???)

Any ideas?

Upvotes: 1

Views: 151

Answers (4)

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

SELECT
    t1.taskID,
    t1.Parent_taskID,
    t1.userID 
FROM task t1
LEFT OUTER JOIN task t2
    ON t1.userID = t2.userID 
       AND t2.taskID = t1.Parent_taskId
WHERE t2.taskID IS NULL

Upvotes: 4

Andomar
Andomar

Reputation: 238286

You could use a not exists subquery to filter out rows with the same parent. This works if there are only two levels of tasks, and tasks cannot have grandchildren.

select  *
from    Table1 parent
where   not exists
        (
        select  *
        from    Table1 child
        where   parent.UserID = child.UserID
                and parent.Parent_TaskID = child.TaskID
        )

Live example at SQL Fiddle.

Upvotes: 1

silly
silly

Reputation: 7887

use left outer join and is null in the where statement like this:

SELECT
    t.taskID,
    t.Parent_taskID,
    t.userID 
FROM task t
LEFT OUTER JOIN task t2
    ON t2.taskID = t.Parent_taskID
    AND t2.userID = t.userID
WHERE ts.taskID IS NULL

Upvotes: 1

Lorenzo L
Lorenzo L

Reputation: 201

Your NOT IN will be

select t.taskID, t.Parent_taskID, t.userID 
from task t
where t.Parent_taskID not in (
    select tp.taskID
    from task tp
    where tp.userID = t.userID 
)

another good (and readable) solution is to use the NOT EXISTS

select t.taskID, t.Parent_taskID, t.userID 
from task t
where not exists (
    select 1
    from task tp
    where tp.taskID = t.Parent_taskID
      and tp.userID = t.userID 
)

Upvotes: 1

Related Questions