SQL to handle multiple AND conditions

Question

I have the following data:

Institution_no	Cred_type
1	1
1	2
2	1

I have a list box so the user can select multiple Cred_type, they want to be able to select either OR or AND conditions.

For the OR I have like this AND CRED_TYPE IN (1,2)

For the AND, I am really scratching my head. What they are saying is they want a list of institutions that have cred type 1 and cred_type 2. Perhaps I'm not thinking clearly but this is row by row, so doing this would lead to no results.

AND cred_type = 1 AND cred_type = 2 -- you can't have a single row have two different values, this would return no results.

They require that the user can select 10, 20, or more, so writing out a bunch of code for each and combining them all would be really tough - but this is the only thought I had so far. It would be like this

Select institution_no from table where cred_type = 1
UNION 
Select institution_no from table where cred_type = 2

-- this would combine both and get me what I want, but you could imagine all the code for 10 or 20 of these.

Answer 1

You can use HAVING clause.

For example: If you have a list of institutions that have cred type 1,2,3,4,5,6 you could try something like:

select institution_no 
from table 
where cred_type in (1,2,3,4,5,6)
group by institution_no
having count(distinct cred_type)=6;

EDIT: Query used by OP

with cte as ( 
             select distinct institution_no, 
                    CERT_TYPE 
             from credentialing 
             where CERT_TYPE in (1,2)
             ) 
select institution_no, 
       count(institution_no) 
from cte 
group by institution_no 
having count(institution_no) = 2;

Answer 2

Here's a solution where you could build off of your OR query for the AND query:

select distinct t2.institution_no from
(select t1.institution_no, count(distinct t1.cred_type) as cnt from
(select institution_no, cred_type from table_name where cred_type in (1,2,3,6,7)) t1
group by t1.institution_no) t2
where cnt=5

• t1 is simply the OR query, but also prints out cred_type
• t2 calculates the number of different cred_types, within the acceptable creds
• the final table prints out the institutions that have all of those creds

That way you only need to update the list of credentials, and the count when they request a bunch of cred_types. If you don't even want to update the count number each time, you could make the list of cred_types a variable, and place it in t1, along with calculating the length of the variable in the where statement.