CODEWITHSUNDEEP
mysqlmysql-workbench
AznDevil92
AznDevil92

Reputation: 554

MySQL Workbench - How to get results from multiple where clauses?

I am trying to pull institution names that has a relation to one or more custodians.

For example, let's say Institution A is has a relation to Custodian 1, 2, and 3 then I want the result to show only Institution A, NOT Institution B and C. Basically I only want to see the institutions that has Custodian 1,2,3, NOT the institutions that has a relationship to only Custodian 1 or 2 or 3.

(Hope the question makes somewhat sense)

Below is the SQL that I am working on:

select i.institution_name, custodian_name
from institution i
join institution_has_custodian ihcu
on i.institution_id = ihcu.institution_id
join custodian c
on c.custodian_id = ihcu.custodian_id
where c.custodian_name in ('JPMorgan (00902/82D)','BNY Mellon (Formerly Bony) (00901/93I)')

Upvotes: 0

Views: 55

Answers (1)

Rahul
Rahul

Reputation: 77896

You can use a outer query and grouping by institution_name as below

select institution_name, count(custodian_name) as cust_count
from
(
select i.institution_name, custodian_name
from institution i
join institution_has_custodian ihcu
on i.institution_id = ihcu.institution_id
join custodian c
on c.custodian_id = ihcu.custodian_id
where c.custodian_name in ('JPMorgan (00902/82D)',
'BNY Mellon (Formerly Bony) (00901/93I)')
) tab
group by institution_name
having cust_count = 3

Upvotes: 1

Related Questions