SQL Select where all joined values equal value

Question

I am trying to use this page as a reference but can't get my situation to work SQL: Select records where ALL joined records satisfy some condition

I have two tables - one is task list and the other is task steps I want to join the tables on taskID and find the taskID's where all steps are marked complete but the overall task is not marked complete yet.

           table1                                   table2
taskID | taskName | taskComplete |             taskID | stepID | stepComplete
   1      task1          0                        1       21          1
   2      task2          0                        1       12          1
                                                  1       34          1
                                                  1       11          1
                                                  2        8          0
                                                  2        6          0
                                                  2        4          1

score 3 · Answer 1 · answered May 05 '15 at 16:09

3

I believe this should do the trick :

SELECT taskID
 FROM table1 t1
 WHERE NOT EXISTS (SELECT 1 
                    FROM table2 
                    WHERE taskID = t1.id
                      AND stepComplete = 0)
  AND t1.taskComplete = 0;

answered May 05 '15 at 16:09

Jean-François Savard

20,626
7
49
76

Eterm · Answer 2 · 2015-05-05T16:14:46.497

2

SQL problems are best attacked by thinking in sets.

You wish to select the set of elements in table 1 which table1.taskComplete is true and all steps in table2 are complete.

This can be rewritten as saying you want to find table1 entries where taskComplete = 0 and there are no entries in table2 where stepComplete = 0.

SELECT table1.*
FROM table1
WHERE table1.taskComplete = 0 AND table1.taskID not in (SELECT taskID FROM table2 WHERE stepComplete = 0)

edited May 05 '15 at 16:14

answered May 05 '15 at 16:10

Eterm

1,698
12
23

*"where all steps are marked complete but the overall task is not marked complete yet."* – Jean-François Savard May 05 '15 at 16:14
1

Thanks @Jean-FrançoisSavard I had that part in my head forgot to add it to the where clause. – Eterm May 05 '15 at 16:15
Note that OP specify he want to retrieve only `taskID`. – Jean-François Savard May 06 '15 at 15:23

score 1 · Answer 3 · answered May 05 '15 at 16:11

You could rephrase the question as "I want to find tasks that are not marked complete yet but where all steps are marked complete", or in other words: "I want to find tasks that are not marked complete yet but don't have any steps marked as incomplete".

When phrased like this, it becomes clear that a not exists operator could do the job, and now it's just a matter of translating English to SQL:

SELECT *
FROM   table1
WHERE  taskComplete = 0 AND
       NOT EXISTS (SELECT *
                   FROM   table2
                   WHERE  table2.taskID = table1.taskID AND stepComplete = 0)

Note that OP specify he want to retrieve only `taskID`. – Jean-François Savard May 06 '15 at 15:23 — Jean-François Savard, May 06 '15 at 15:23

SQL Select where all joined values equal value

3 Answers3