Oracle SQL Query to lists the companies that work at least in all the domains that company with id 1 works in

user_13104396 0
Viewed 48

Oracle SQL Query to lists the companies that work at least in all the domains that company with id 1 works in.

and answer should be id= 3 and 7 because 1, 3 and 7 are using domains Education and Energy

CREATE TABLE Company_Domain (id NUMBER, Domain_Name VARCHAR(30));

BEGIN
  INSERT INTO Company_Domain VALUES (1 , 'Education');
  INSERT INTO Company_Domain VALUES (1 , 'Energy');
  INSERT INTO Company_Domain VALUES (2 , 'IT');
  INSERT INTO Company_Domain VALUES (2 , 'Marketing');
  INSERT INTO Company_Domain VALUES (3 , 'Energy');
  INSERT INTO Company_Domain VALUES (3 , 'Education');
  INSERT INTO Company_Domain VALUES (4 , 'Food');
  INSERT INTO Company_Domain VALUES (4 , 'Energy');
  INSERT INTO Company_Domain VALUES (5 , 'Marketing');
  INSERT INTO Company_Domain VALUES (5 , 'Law');
  INSERT INTO Company_Domain VALUES (6 , 'IT');
  INSERT INTO Company_Domain VALUES (6 , 'Banking');
  INSERT INTO Company_Domain VALUES (7 , 'Education');
  INSERT INTO Company_Domain VALUES (7 , 'Energy');
  INSERT INTO Company_Domain VALUES (8 , 'Marketing');
  INSERT INTO Company_Domain VALUES (8 , 'IT');
END;
/
id Domain_Name
1 Education
1 Energy
2 IT
2 Marketing
3 Energy
3 Education
4 Food
4 Energy
5 Marketing
5 Law
6 IT
6 Banking
7 Education
7 Energy
8 Marketing
8 IT

regards

oracle sql
1 Answers

Select:

select id
from company_domain
where domain_name
  in (select domain_name from company_domain where id = 1)
  and id <> 1
group by id
having count(domain_name)
  = (select count(domain_name) from company_domain where id = 1);

Output:

ID
7
3

Rows in the table Company_name must be unique.

user_115822700
Related