sql intersect with dynamic input set

Question

I'm trying to figure out how to get the intersection of a dynamic input set. Here is a very simplified example.

company_status table:

COMPANY | STATUS
----------------
  Big   |   1
Notused |   0
 Small  |   1

company_country table:

COMPANY | COUNTRY
-----------------
  Big   |   CA
  Big   |   US
Notused |   CA
Notused |   FR
 Small  |   US
 Small  |   IT

What I want is the intersection of the countries for only certain companies.

If I select only companies where status = 1, here is my expected output:

US

If I select only companies where status = 0, here is my expected output:

CA
FR

Taking the company_status table out of the equation, this is what I need:

select country from company_status where company = 'Big'
intersect
-- ... (here is where the dynamic part comes in)
intersect
select country from company_status where company = 'Small';

But how do I add company_status into this?

Answer 1

If I understand correct so you want only these countries, which fulfil in every row of company_status the condition status = 1 or status = 0.

If so you could count, how many appearances are to be found in company_status and use this in the having- clause. But you of course have to put the same condition into the where- clause of the join.

WITH 
company_status as (
  select 'BIG' COMPANY, 1 STATUS from dual union all
  select 'NOTUSED' COMPANY, 0 STATUS from dual union all
  select 'SMALL' COMPANY, 1 STATUS from dual
),
company_country as (
  select 'BIG' COMPANY, 'CA' COUNTRY from dual union all
  select 'BIG' COMPANY, 'US' COUNTRY from dual union all
  select 'NOTUSED' COMPANY, 'CA' COUNTRY from dual union all
  select 'NOTUSED' COMPANY, 'FR' COUNTRY from dual union all
  select 'SMALL' COMPANY, 'US' COUNTRY from dual union all
  select 'SMALL' COMPANY, 'IT' COUNTRY from dual
)
select cc.country
from company_country cc join
     company_status cs
     on cc.company = cs.company
where cs.status = 0
group by cc.country
having count(*) = (SELECT COUNT(*) FROM company_status where status = 0);

(the with- clause only gives your rows, the rest of the question should work on your example)

But you can use with- clause to determine, which Status you want at only one place (third with- clause, would be your first):

WITH 
company_status as (
  select 'BIG' COMPANY, 1 STATUS from dual union all
  select 'NOTUSED' COMPANY, 0 STATUS from dual union all
  select 'SMALL' COMPANY, 1 STATUS from dual
),
company_country as (
  select 'BIG' COMPANY, 'CA' COUNTRY from dual union all
  select 'BIG' COMPANY, 'US' COUNTRY from dual union all
  select 'NOTUSED' COMPANY, 'CA' COUNTRY from dual union all
  select 'NOTUSED' COMPANY, 'FR' COUNTRY from dual union all
  select 'SMALL' COMPANY, 'US' COUNTRY from dual union all
  select 'SMALL' COMPANY, 'IT' COUNTRY from dual
),
wished_status as (select 0 wished_status from dual)
select cc.country
from company_country cc 
join company_status cs on cc.company = cs.company
JOIN wished_status s on cs.status = s.wished_status
group by cc.country
having count(*) = (SELECT COUNT(*) FROM company_status cs join wished_status s on cs.status = s.wished_status);

so you only had to Change between 0 and 1 (or whatever you need) in wished_status