3

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.

2
  • @ErgestBasha Good idea but unfortunately they have multiple duplications so sometimes inst 1 could have 3 rows of cred_type 1. So just doing having ... = 2 will not work.
    – Rob
    Commented Feb 2, 2022 at 20:51
  • See Erwin's answer here: How to filter SQL results in a has-many-through relation where there are 10+ methods to write this kind of query (and performance testing in Postgres). The problem is called (and we have a tag for it): relational-division Commented Feb 3, 2022 at 11:52

2 Answers 2

6

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;
2
  • 1
    having count(distinct cred_type)=6 to deal with duplicates. Commented Feb 2, 2022 at 20:57
  • @ErgestBasha thanks so much. The way I did it was this - this gets rid of the duplicate rows 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;
    – Rob
    Commented Feb 2, 2022 at 21:14
1

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.

2
  • 1
    the other solution actually looks better, but you could still leverage my variable idea if you'd like to cut down on changes to the query each time. Commented Feb 2, 2022 at 21:05
  • This is really clever!
    – Rob
    Commented Feb 4, 2022 at 12:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.