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

Try this please. I checked it and it's working:

SELECT * FROM Table WHERE ARIDNR IN (     SELECT ARIDNR     FROM Table     GROUP BY ARIDNR     HAVING COUNT(distinct LIEFNR) > 1 ) 

Answer 2

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.