SQL Query to select the rows with same id but different value in another column.

Description:

In this example we explain that how to select the rows with same id but different value in another column (parent column) in SQL Server. Or how to get all rows that have same ID but different value in other column. Or select only those records that have different/multiple values for a particular column in sql server. Or find record in same table with different company id but some other some values.

Query:

For example suppose I have data like

+------+------+

|ARIDNR|LIEFNR|

+------+------+

|1     |A     |

+------+------+

|2     |A     |

+------+------+

|3     |A     |

+------+------+

|1     |B     |

+------+------+

|2     |B     |

+------+------+

And I want output like this

+------+------+

|ARIDNR|LIEFNR|

+------+------+

|1     |A     |

+------+------+

|1     |B     |

+------+------+

|2     |A     |

+------+------+

|2     |B     |

+------+------+

Below is the query to get the result like

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

In my case the query is

SELECT *

FROM ReinforcementRequest

WHERE ReinforcementOrderId IN (

    SELECT ReinforcementOrderId

    FROM ReinforcementRequest

    GROUP BY ReinforcementOrderId

    HAVING COUNT(distinct CreditorId) > 1

)

SELECT * FROM ReinforcementRequest WHERE ReinforcementOrderId IN (

    SELECT a.ReinforcementOrderId FROM ReinforcementRequest a

    JOIN ReinforcementRequest b on b.ReinforcementOrderId = a.ReinforcementOrderId AND b.CreditorId <> a.CreditorId