[Resolved] did not complete task query help

fikse

I have a members table with 1000 records:

memberID
firstname
lastname

a tasks_types table with 50 records:

taskID
taskdescription

and a tasks table

memberID
taskID

when a member completes a task, a record is added to the tasks table with their memberID and the taskID that they completed.

I'm looking for the most efficient way to find which members did not complete a given task or set of tasks.....

Putting all of the memberID's into an array, and then querying each one against the tasks table for a given taskID would work, but that's 1000 query's each time....

Selecting all of the memberID's from the tasks table for a given taskID would give me everyone who did complete the task... maybe then selecting all of the members from the members table that are not in that list that completed the task? is there an easy way to do that?

any suggestions on the proper query for this would be great....

nemonoman

SELECT *
FROM member
LEFT JOIN task ON
task.memberID=member.memberID
WHERE task.memberID IS NULL

fikse

thanks... this helps... that query doesn't specify which of the 50 tasks I'm trying to see that they didn't complete but I think I can add it in...

fikse

ok, how about selecting all members that competed two specific tasks? this is what I'm trying, and it's not working...

SELECT * 
FROM members 
LEFT JOIN tasks ON tasks.memberID = member.memberID AND (tasks.taskID = '1' AND tasks.taskID = '10') 
WHERE tasks.memberID IS NOT NULL

thanks for any more help....

nemonoman

Time to RTFM:

Members who didn't do tasks 1 and 10

SELECT *

FROM members

LEFT JOIN tasks ON tasks.memberID = member.memberID WHERE tasks.memberID IS NULL
AND (tasks.taskID = '1' OR tasks.taskID = '10')

Members who did do tasks 1 and 10

SELECT *

FROM members

INNER JOIN tasks ON tasks.memberID = member.memberID WHERE (tasks.taskID = '1' OR tasks.taskID = '10')

fikse

I appreciate the assistance... the query below brings up all the members who completed either task 1 or 10, but I need all of the members who completed both 1 and 10....

changing the OR to AND in the query below doesn't work...

thanks again...

Originally posted by nemonoman

Members who did do tasks 1 and 10

SELECT *

FROM members

INNER JOIN tasks ON tasks.memberID = member.memberID WHERE (tasks.taskID = '1' OR tasks.taskID = '10') [/B]

fikse

here is a working query.... thanks to r937....

SELECT * FROM members 
INNER JOIN requirements ON members.memberID = requirements.memberID 
WHERE requirements.requirementID IN (10,1) 
GROUP BY members.memberID HAVING count(*) = 2