Select rows with same id but different value in another column

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     |
+------+------+

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
)

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.

Join the same table back to itself. Use an inner join so that rows that don't match are discarded. In the joined set, there will be rows that have a matching ARIDNR in another row in the table with a different LIEFNR. Allow those ARIDNR to appear in the final set.

SELECT * FROM YourTable WHERE ARIDNR IN (
    SELECT a.ARIDNR FROM YourTable a
    JOIN YourTable b on b.ARIDNR = a.ARIDNR AND b.LIEFNR <> a.LIEFNR
)

This is an old question yet I find that I also need a solution for this from time to time. The previous answers are all good and works well, I just personally prefer using CTE, for example:

DECLARE @T TABLE (ARIDNR INT, LIEFNR varchar(5)) --table variable for loading sample data
INSERT INTO @T (ARIDNR, LIEFNR) VALUES (1,'A'),(2,'A'),(3,'A'),(1,'B'),(2,'B'); --add your sample data to it
WITH duplicates AS --the CTE portion to find the duplicates
(
	SELECT ARIDNR FROM @T GROUP BY ARIDNR HAVING COUNT(*) > 1
)
SELECT t.* FROM @T t --shows results from main table
INNER JOIN duplicates d on t.ARIDNR = d.ARIDNR --where the main table can be joined to the duplicates CTE

---

Yields the following results:
> 1|A
> 1|B
> 2|B
> 2|A

Select A.ARIDNR,A.LIEFNR
from Table A
join Table B
on A.ARIDNR = B.ARIDNR
and A.LIEFNR<> B.LIEFNR
group by A.ARIDNR,A.LIEFNR

You can simply achieve it by

SELECT *
FROM test
WHERE ARIDNR IN
    (SELECT ARIDNR FROM test
     GROUP BY ARIDNR
     HAVING COUNT(*) > 1)
GROUP BY ARIDNR, LIEFNR;

Thanks.

Use this

select * from (
 SELECT ARIDNR,LIEFNR,row_number() over 
     (partition by ARIDNR order by ARIDNR) as RowNum) a
where a.RowNum >1

$sql="SELECT * FROM TABLE_NAME WHERE item_id=".$item_id;

$query=mysql_query($sql);

while($myrow=mysql_fetch_array($query)) {

echo   print_r($myrow,1);


}