CODEWITHSUNDEEP
mysqlsqlselect
vertigoelectric
vertigoelectric

Reputation: 1327

MySQL - Select records in a table where none of the associated records in another table equal a certain value

Sorry if this duplicates someone else's question, but I've been working on this for a long time and haven't found a solution.

I have two tables, todo_list (which contains a list of TODO lists) and task (which contains a list of TASKS, each of which is associated with one of the TODO lists).

Here's the relevant structure:

todo_list

task

Multiple tasks are assigned to each TODO list. I want to be able to select the TODO lists for which all associated tasks are deleted. How can I do this?

Upvotes: 1

Views: 415

Answers (3)

Meherzad
Meherzad

Reputation: 8553

Try this query 

select distinct tl.id, tName from todo_list tl, task t where 
tl.id=t.todo_list_id and
tl.id not in (select todo_list_id from task where deleted =1);

Note : Assuming that deleted records will have value 0 if it is 1 change the value to 0 in the query

http://sqlfiddle.com/#!2/01b27/9

Hope this helps

Upvotes: 0

John Flatness
John Flatness

Reputation: 33759

You could do a left join with the join condition specifying only non-deleted tasks. Then, the unjoined rows would be those that have no tasks where deleted = 0.

SELECT todo_list.* FROM todo_list
LEFT JOIN task ON todo_list.id = task.todo_list_id && task.deleted = 0
WHERE task.todo_list_id IS NULL

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269643

I think the best approach for these types of queries is aggregation:

select todo_list_id
from task t
group by todo_list_id
having sum(deleted) = count(*)

Or, an alternative having clause is:

having max(case when deleted = 0 then 1 else 0 end) = 0

If you are trying to get lists where all tasks are either deleted or undeleted, the following having clause works:

having min(deleted) <> max(deleted)

Upvotes: 2

Related Questions