CODEWITHSUNDEEP
ms-access
Karen
Karen

Reputation: 11

How to select value if only all criteria are met?

First time Poster here so I appoligize about the formatting and am really novice at sql, but this has me stumped. That and I am using 2016 MS Access's SQL as well.

I have a table and I want to select only the names of the people who have fulfilled all the requirements.

Table Chore

ID Name   Chore    Done
1  Joe    Sweep    Yes
2  Joe    Cook     Yes
3  Joe    Dust     Yes
4  Bill   Vacuum   No
5  Bill   Dust     Yes
6  Carrie Bathroom Yes
7  John   Cook     No
8  John   Beds     No
9  John   Laundry  Yes
10 Mary   Laundry  No
11 Mary   Sweep    No
12 Cindy  Car      Yes
13 Cindy  Garden   Yes

In this case, only Joe, Carrie and Cindy's names should be returned because under their name, they finished all their chores.

Help please and thanks in advance!

Upvotes: 1

Views: 47

Answers (2)

Beth
Beth

Reputation: 9607

You could check the value of max(done), like

select
   name
from
   my_table
group by name
having max(done) = -1

In Access, Yes/True is -1, No/False is 0, so max(done) is Yes

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133360

You can use not in 

select name from my_table  
where name not in (select name from my_table where  chore_done ='No');

Upvotes: 2

Related Questions