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;
/

regards

CodePudding user response：

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

TABLE Name: Company_Domain Columns: id NUMBER and Domain_Name VARCHAR(30)

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

CodePudding user response：

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:

Rows in the table Company_name must be unique.