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

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

(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)

CodePudding user response：

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

with test_table(ID, STR_ROUTE, STR_STREET) as (
  select 1, 'MAIN', 'Can' from dual union all
  select 2, 'AV', 'CAL' from dual union all
  select 3, 'CLL', 'CLL' from dual union all
  select 4, 'STR', 'VAL' from dual union all
  select 5, 'VAL', 'MIN' from dual union all
  select 7, 'CAL', 'SQR' from dual
)
select *
from test_table
where str_route in (
  select f.str_street
  from test_table f
)
  and str_route != str_street

union all

select *
from test_table
where str_street in (
  select f.str_route
  from test_table f
)
  and str_route != str_street

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

db<>fiddle here

CodePudding user response：

You can use a hierarchical query and filter to only include the rows where the hierarchical query has descended to the second level and it is a leaf node or where the first level of the hierarchy is not a leaf (and there is a matching second level in the hierarchy):

SELECT *
FROM   table_name
WHERE  LEVEL = 1 AND CONNECT_BY_ISLEAF = 0
OR     LEVEL = 2 AND CONNECT_BY_ISLEAF = 1
CONNECT BY NOCYCLE
       PRIOR str_route = str_street;

Which, for the sample data:

CREATE TABLE table_name (ID, STR_ROUTE, STR_STREET) AS
SELECT 1, 'MAIN', 'Can' FROM DUAL UNION ALL
SELECT 2, 'AV',   'CAL' FROM DUAL UNION ALL
SELECT 3, 'CLL',  'CLL' FROM DUAL UNION ALL
SELECT 4, 'STR',  'VAL' FROM DUAL UNION ALL
SELECT 5, 'VAL',  'MIN' FROM DUAL UNION ALL
SELECT 7, 'CAL',  'SQR' FROM DUAL;

Outputs:

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

db<>fiddle here