SQL- Show only users with certain awards

Question

I have an Access user database similar to

UserName |
Award |
DateGiven

We assign various types of awards. Each award assignment would have a separate entry. I need to be able to find which users have been assigned 3 specific awards.

For instance:

We assign award a, b, c, d, e, and f.

john, a, 01/01/14|
bill, b, 02/02/14|
john, c, 01/02/14|
mary, a, 01/01/14|
sue, e, 01/01/14|
john, b, 01/01/14|

I need to be able to show which users have been assigned award a and b and c, so from the above it would only show John.

Ideas?

Have you tried anything so far? – Kathir May 01 '14 at 16:57 — Kathir, May 01 '14 at 16:57

score 4 · Answer 1 · answered May 01 '14 at 16:37

4

select username
from your_table
where award in ('a','b','c')
group by username
having count(distinct award) = 3

answered May 01 '14 at 16:37

juergen d

201,996
37
293
362

score 0 · Answer 2 · answered Feb 22 '19 at 07:07

0

select username
from your_table
where award in ('a','b','c','d','e','f');

answered Feb 22 '19 at 07:07

Sayan

93
3
9

1

Can you explain that further? How does this query check for the assignment of "3 specific awards"? – Nico Haase Feb 22 '19 at 07:28
This is a duplicate of another answer that was posted 5 years ago. – karel Feb 22 '19 at 08:07

SQL- Show only users with certain awards

2 Answers2