Find MySQL entries with same set of column B values for their common column A value

Question

I have:

+----+------+------+
| id | A    | B    |
+----+------+------+
|  1 |    1 |    1 |  <
|  2 |    1 |    2 |  <
|  3 |    2 |    2 | 
|  4 |    2 |    3 | 
|  5 |    3 |    1 |  <
|  6 |    3 |    2 |  <
|  7 |    4 |    4 | 
|  8 |    4 |    5 | 
+----+------+------+

I need to select the entries that have the same set of B values for their common A value. So, in this case, I need ids 1, 2 and 5, 6 because in those cases the sequence of B values is 1, 2.

Is this possible?

Thanks!

Answer 1

Is this what you want?

SELECT t1.ID
FROM TableName AS t1
  INNER JOIN TableName AS t2 ON t1.B=t2.B AND NOT t1.A=t2.A

Answer 2

Here's what I came up with:

SELECT IDs FROM
(SELECT GROUP_CONCAT(id) AS IDs, a FROM foo GROUP BY a) Y
WHERE a IN
(SELECT Ta.a FROM (SELECT a, GROUP_CONCAT(b) AS c FROM foo GROUP BY a) Ta
JOIN (SELECT a, GROUP_CONCAT(b) AS c FROM foo GROUP BY a) Tb USING (c) WHERE T
a.a <> Tb.a)

with the result:

+------+
| IDs  |
+------+
| 1,2  |
| 5,6  |
+------+
2 rows in set (0.00 sec)

The idea is that an equi-join on SELECT a, GROUP_CONCAT(b) AS c FROM foo GROUP BY a gives us the values of a that have the same values of b. (This would probably need to be refined as a correlated subquery as more data is added to the table.)

Then we select the IDs that have the value of a that is in the list of a with the same values of b.