Menu
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 | +------+------+ 
847
Comparison of columns in the same table is possible with the help of joins. Here we are comparing all the customers that are in the same city using the self join in SQL. Self-join is a regular join where a table is joined by itself. Similarly, a table may be joined with left join, right join, inner join, and full join.
For example, if you are grouping by DATEPART (yyyy, <column name>), use GROUPING_ID (DATEPART (yyyy, <column name>)); or if you are grouping by <column name>, use GROUPING_ID (<column name>).
The Problem you have is that you are putting an AND constraint which will never be true, Hence you are not getting any output. This query will do the trick. Show activity on this post. Another solution to this problem is joining itself on a subquery which gets the lastest date for each FKID .
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.
24
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
