Return all the same items from different groups
-------------------
- TABLE -
-------------------
ID | GRUPO | OBJETO
---|-------|-------
1 | 1 | 1
2 | 1 | 2
| |
3 | 2 | 1
4 | 2 | 2
| |
5 | 3 | 1
6 | 3 | 2
7 | 3 | 3
| |
5 | 4 | 1
6 | 4 | 3
| |
7 | 5 | 1
I need to make a query to return all groups that contains exactly the same objects. I've used subquery with IN but the objects 1 e 2 they are also in Group 3 .
For example, if I query using where GRUPO = 1, I need it to return only a total of two records (Group 1 and Group 2).
Group objects 1 (1 e 2), they are also in Group 2. But Group 3 contains one object in addition, Group 4 contains two objects but one of them is different from those in Group 1, and Group 5 contains only one object; therefore, groups 3, 4 and 5 should not be considered.
There are some relationships but they don't make much difference in the case.
- Group 1 bought: orange and Apple
- Group 2 bought: orange and Apple
- Group 3 bought: orange, apple and banana
If I query by GRUPO = 1, I need to return only and Only who bought the mostonly orange and Apple . I don't care what groups orange and Apple are in, so IN doesn't work.
4 answers
I think the following solution returns the result you want.
This query returns all groups that have exactly the same elements as Group 1.
SELECT T.GRUPO
FROM TESTE T
LEFT JOIN
(
SELECT DISTINCT T1.OBJECTO,
(SELECT COUNT(DISTINCT T2.OBJECTO)
FROM TESTE T2
WHERE T2.GRUPO = T1.GRUPO) TOTAL
FROM TESTE T1
WHERE T1.GRUPO = 1 --filtras aqui o grupo
) SS
ON SS.OBJECTO = T.OBJECTO
GROUP BY T.GRUPO
HAVING COUNT(DISTINCT T.OBJECTO) = COUNT(DISTINCT CASE WHEN SS.OBJECTO IS NOT NULL THEN SS.OBJECTO END)
AND COUNT(DISTINCT T.OBJECTO) = MAX(TOTAL)
The logic behind this query is as follows:
To subquery
SELECT DISTINCT T1.OBJECTO,
(SELECT COUNT(DISTINCT T2.OBJECTO)
FROM TESTE T2
WHERE T2.GRUPO = T1.GRUPO) TOTAL
FROM TESTE T1
WHERE T1.GRUPO = 1
Aims to return all objects in Group 1. This is the basis for the process. Initially, this statement only returned the objects, it was necessary to change to also return the total number of objects.
With this result we have everything we need to look for the remaining groups that have exactly the same elements. This is done through the following instructions:
HAVING COUNT(DISTINCT T.OBJECTO) = COUNT(DISTINCT CASE WHEN SS.OBJECTO IS NOT NULL THEN SS.OBJECTO END)
AND COUNT(DISTINCT T.OBJECTO) = MAX(TOTAL)
The first means, " all elements of Group (COUNT (DISTINCT T. object)) must be in Group 1 (COUNT (DISTINCT CASE when SS.OBJECT IS NOT NULL THEN SS.END OBJECT))"
Finally, the statement COUNT (DISTINCT T. object) = MAX (TOTAL) guarante that the group has to have the same number of elements as Group 1. This instruction is essential to exclude groups that, as in your example, only have orange. Orange is in Group 1 but is fantasizing about the Apple.
MAX is necessary because in the having statement We can only use aggregation functions or constants.
If you want to get the objects for each of the groups (the objects will always be the same), just use the previous query to filter the results, for example like this
SELECT T.GRUPO,
T.OBJECTO
FROM TESTE T
INNER JOIN
(
SELECT T.GRUPO
FROM TESTE T
LEFT JOIN
(
SELECT DISTINCT T1.OBJECTO,
(SELECT COUNT(DISTINCT T2.OBJECTO)
FROM TESTE T2
WHERE T2.GRUPO = T1.GRUPO) TOTAL
FROM TESTE T1
WHERE T1.GRUPO = 1 --filtras aqui o grupo
) SS
ON SS.OBJECTO = T.OBJECTO
GROUP BY T.GRUPO
HAVING COUNT(DISTINCT T.OBJECTO) = COUNT(DISTINCT CASE WHEN SS.OBJECTO IS NOT NULL THEN SS.OBJECTO END)
AND COUNT(DISTINCT T.OBJECTO) = MAX(TOTAL)
) X
ON T.GRUPO = X.GRUPO
ORDER BY 1, 2
Here is also the SQLFiddle
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 better visualization, 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 (-) they were left out of the result, given that their object does not correspond to any of the objects in Group 1.
So see that so far I only return 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 will filter to keep only groups whose totalization of objects is equal to the total of objects in Group 1, by adding the clause having :
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 leave because although its count has stayed at 2 (because it actually has two objects coinciding with the objects of Group 1), it actually has 3 objects in the base (one of them does not coincide with any objects of Group 1 and so it was left out of the count).
Then I add one more condition in having , determining that only the groups whose total of objects in the base is equal to the total of objects in Group 1:
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 the count from the select because I don't want it in the result View, and I get to the query that was presented at the beginning of the response.
See in SQL Fiddle .
The only way I know how to do it is with GROUP_CONCAT, and since it is not possible to do join with computed fields it will take two subqueries (equal, you can even create a view):
SELECT t2.*
FROM (
SELECT grupo
, GROUP_CONCAT(objeto ORDER BY objeto) objetos
FROM tabela GROUP BY grupo
) t1 INNER JOIN (
SELECT grupo
, GROUP_CONCAT(objeto ORDER BY objeto) objetos
FROM tabela GROUP BY grupo
) t2 on t1.objetos = t2.objetos
WHERE t1.grupo = 1
I did a SQL Fiddle demonstrating how it works with Group 3, which has 3 objects.
Follows SQL
SELECT count(distinct grupo,objecto) as tabela FROM tabela WHERE objecto in (1,2) and grupo IN (1,2) group by objecto.
Make sure that's what you want. Next time try to be clearer.