sql - Select rows with same id but different value in another column

Question

I tried for hours and read many posts but I still can't figure out how to handle this request:

I have a table like this:

+------+------+
|ARIDNR|LIEFNR|
+------+------+
|1     |A     |
+------+------+
|2     |A     |
+------+------+
|3     |A     |
+------+------+
|1     |B     |
+------+------+
|2     |B     |
+------+------+

I would like to select the ARIDNR that occurs more than once with the different LIEFNR.

The output should be something like:

+------+------+
|ARIDNR|LIEFNR|
+------+------+
|1     |A     |
+------+------+
|1     |B     |
+------+------+
|2     |A     |
+------+------+
|2     |B     |
+------+------+

Answer 1

This ought to do it:

SELECT *
FROM YourTable
WHERE ARIDNR IN (
    SELECT ARIDNR
    FROM YourTable
    GROUP BY ARIDNR
    HAVING COUNT(*) > 1
)

The idea is to use the inner query to identify the records which have a ARIDNR value that occurs 1+ times in the data, then get all columns from the same table based on that set of values.