Trouble scanning and extracting from tables

[j05h]

I have three tables that I'm trying to get information from. I need to know how to extract users that perform either of two tasks but not another. Users can do one, two or all three tasks

user-id | user-name
--------+----------
1 | joe
2 | beth
3 | tim

task-id | task-name
--------+----------
1 | ls
2 | man
3 | grep

user-id | task-id
--------+---------
2 | 1
2 | 3
1 | 1
3 | 2

I want to select all user-name that perform task-name "ls" or "man" but do not perform task-name "grep".

Please help.

 

[PHV]

One way:
SELECT DISTINCT U.user_name
FROM tblUserTask UT
INNER JOIN tblTask T ON UT.task_id = T.task_id
INNER JOIN tblUser ON UT.user_id = U.user_id
WHERE T.task_name IN ('ls','man')
AND U.user_id NOT IN (
SELECT user_id FROM tblUserTask X INNER JOIN tblTask Y
ON X.task_id = Y.task_id WHERE Y.task_name = 'grep')

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[j05h]

Thanks