Skip to content
Advertisement

How to find duplicate values according multiple columns and show both

I try to find another examples, but I have not been able to find one that can help me

I am currently trying to find if the value in the STR_ROUTE column is in the STR_STREET column, as shown in the following example

ID STR_ROUTE STR_STREET
1 MAIN Can
2 AV CAL
3 CLL CLL
4 STR VAL
5 VAL MIN
7 CAL SQR

in this example as the CAL and VAL values of the STR_ROUTE column are in STR_STREET the expected result is to display the following table with all occurrences

ID STR_ROUTE STR_STREET
2 AV CAL
4 STR VAL
5 VAL MIN
7 CAL SQR

(The third row is not taken into consideration because it is the same registry.)

I was validating with this option, but I have not been able to succeed and does not take the rules into consideration.

  1. It does not take into consideration if the repeated value is within the same record.
  2. Both the repeated record and the record to which it is compared must be displayed.

SELECT * FROM TABLE WHERE STR_ROUTE IN (SELECT STR_STREET FROM TABLE WHERE STR_STREET)

Advertisement

Answer

You may check the presence of values of each column in another column and union the results.

ID STR_ROUTE STR_STREET
5 VAL MIN
7 CAL SQR
2 AV CAL
4 STR VAL

db<>fiddle here

User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement