Get rows having different values for a column based on the duplicate values of combination of other 3 columns

Question

I want to get only rows having a different values in a column(column name DEF) based on the duplicate rows having unique combination of other 3 columns.

Example: In the below example first two rows has same value for first 3 columns.But they have different value for column DEF. So both these rows to be listed in output.

But rows 2 and 4 has unique combination for first 3 columns but they have same values in DEF column.So not to be listed in output.

rows 5 and 6 are not to be listed since they are single row with different values.

+----------+-------+--------+--------+
| dept | role1 |role2 |DEF |
+----------+-------+--------+--------+
| a | abc | er | 0 |
| a | abc | er | 1 |
| b | qwer | ty | 0 |
| b | qwer | ty | 0 |
| c | der | ui | 1 |
| d | nerr | io | 0 |
+----------+-------+--------+--------+
output
+----------+------+------+------+
| dept | role1|role2 |DEF |
+----------+------+------+------+
| a | abc | er |0 |
| a | abc | er |1 |
+----------+------+------+------+

I tried using distinct with having but not able to check the values of column DEF to get desired result.

Can anyone help me on this?

Answer 1

Using standard SQL on most RDBMS, there are various ways.

Using a subquery:

SELECT d.dept, d.role1, d.role2, DEF
FROM data d
INNER JOIN (
 SELECT dept, role1, role2 
 FROM data
 GROUP BY dept, role1, role2
 HAVING COUNT(distinct DEF) > 1
) dup
 ON dup.dept = d.dept AND dup.role1 = d.role1 AND dup.role2 = d.role2
;

The subquery returns sets of dept/role1/role2 with more than 1 distinct DEF.

Using a correlated subquery:

SELECT d.dept, d.role1, d.role2, DEF
FROM @data d
WHERE EXISTS (
 SELECT 1 
 FROM @data 
 WHERE dept = d.dept AND role1 = d.role1 AND role2 = d.role2 AND DEF <> d.DEF
);

The subquery return 0 to n rows. If at least one row exists, the row from the main table is returned.

Using CROSS APPLY:

SELECT d.dept, d.role1, d.role2, d.DEF
FROM @data d
CROSS APPLY (
 SELECT n=1 
 FROM @data 
 WHERE dept = d.dept AND role1 = d.role1 AND role2 = d.role2 AND DEF <> d.DEF
) ca
;

CROSS APPLY works with Oracle or SQL Server.

Output:

dept role1 role2 DEF
a abc er 0
a abc er 1