josef_skywalker josef_skywalker - 3 months ago 38
SQL Question

Select rows with same id but different value in another column

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

I got an table like this

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


What i would like to select is the ARIDNR that occurs more than on times.
With the different LIEFNR.

Output should be something like:

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


Thank you for your help in advance
regards
chris

Answer

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.

Comments