SQL Query to get all rows with duplicate values but are not part of the same group

Question

The database schema is organized as follows:

ID | GroupID | VALUE
--------------------
 1 |    1    |   A
 2 |    1    |   A 
 3 |    2    |   B
 4 |    3    |   B

In this example, I want to GET all Rows with duplicate VALUE, but are not part of the same group. So the desired result set should be IDs (3, 4), because they are not in the same group (2, 3) but still have the same VALUE (B).

I'm having trouble writing a SQL Query and would appreciate any guidance. Thanks.

So far, I'm using SQL Count, but can't figure out what to do with the GroupId.

SELECT * 
FROM TABLE T
HAVING COUNT(T.VALUE) > 1
GROUP BY ID, GroupId, VALUE

Solution 1

The simplest method for this is using EXISTS:

SELECT 
    ID 
FROM
    MyTable T1
WHERE
    EXISTS (SELECT 1
            FROM MyTable 
            WHERE Value = t1.Value
            AND GroupID <> t1.GroupID)

Solution 2

Here is one method. First you have to identify the values that appear in more than one group and then use that information to find the right rows in the original table:

select *
from t
where value in (SELECT value
                FROM TABLE T
                GROUP BY VALUE 
                HAVING COUNT(distinct groupid) > 1
               )
order by value

Actually, I prefer a slight variant in this case, by changing the HAVING clause:

HAVING min(groupid) <> max(groupid)

This works when you are looking for more than one group and should be faster than the COUNT DISTINCT version.

Comments

[JNK]

I don't know what to make of this syntax. Why on earth are you doing a CARTESIAN? Why do you add an underscore to your table aliases?