Solution:
select A.GRUPO
from TABELA A join TABELA B on B.OBJETO = A.OBJETO
where B.GRUPO = 1
group by A.GRUPO
having count(A.OBJETO) = (select count(OBJETO) from TABELA where GRUPO = 1)
and count(A.OBJETO) = (select count(OBJETO) from TABELA where GRUPO = A.GRUPO)
This query works and seems pretty simple to me.
Explaining the line of reasoning:
With the first part of the query (from select to where ) I return records that have at least one of the objects in group 1.
For a better view, see this first part of the slightly modified query:
select A.ID, A.GRUPO, A.OBJETO
from TABELA A join TABELA B on B.OBJETO = A.OBJETO
where B.GRUPO = 1
The result of this query would be:
-------------------
ID | GRUPO | OBJETO
---|-------|-------
1 | 1 | 1
2 | 1 | 2
| |
3 | 2 | 1
4 | 2 | 2
| |
5 | 3 | 1
6 | 3 | 2
-7 | 3 | 3
| |
8 | 4 | 1
-9 | 4 | 3
| |
10 | 5 | 1
The records marked - ) are left out of the result since their object does not match any of the objects in group 1.
Then I see that until now I only return the groups that have some object of group 1, and also their respective object.
Let's now visualize the grouping of this:
select A.GRUPO, count(A.OBJETO)
from TABELA A join TABELA B on B.OBJETO = A.OBJETO
where B.GRUPO = 1
group by A.GRUPO
Result:
---------------------
GRUPO | COUNT(OBJETO)
------|--------------
1 | 2
2 | 2
3 | 2
4 | 1
5 | 1
Now I'm going to filter to keep only the groups whose aggregation of objects is equal to the total of objects in group 1, by adding the having clause:
select A.GRUPO, count(A.OBJETO)
from TABELA A join TABELA B on B.OBJETO = A.OBJETO
where B.GRUPO = 1
group by A.GRUPO
-- novo:
having count(A.OBJETO) = (select count(OBJETO) from TABELA where GRUPO = 1)
Result
---------------------
GRUPO | COUNT(OBJETO)
------|--------------
1 | 2
2 | 2
3 | 2
Group 3 has to quit because although its count has been set to 2 (because it actually has two objects matching the objects in group 1), actually it has 3 objects in the base (one of them does not match any objects in group 1 and so was left out of count ).
So I add one more condition in having , determining that only groups whose total objects on the base are equal to the total of objects in group 1 are kept:
select A.GRUPO, count(A.OBJETO)
from TABELA A join TABELA B on B.OBJETO = A.OBJETO
where B.GRUPO = 1
group by A.GRUPO
having count(A.OBJETO) = (select count(OBJETO) from TABELA where GRUPO = 1)
-- novo:
and count(A.OBJETO) = (select count(OBJETO) from TABELA where GRUPO = A.GRUPO)
Finally, I remove count from select because I do not want it in the result view, and I get to the query that was presented at the beginning of the answer. >
See the SQL Fiddle .