I want to select cases from one table, where Code or DST or Short_text or long_text are equal(in 2 or more rows) AND ID are not equal.
ID Code DST Short_text Long_text
1 B 01 B 1 Bez1 Bezirk1
1 B 01 B 1 Bez1 Bezirk1
2 B 02 B 2 Bez2 Bezirk2
3 B 03 B 3 Bez3 Bezirk3
4 B 04 B 4 Bez4 Bezirk4
4 B 04 B 4 Bez4 Bezirk4
5 B 05 B 5 Bez5 Bezirk5
6 B 06 B 6 Bez6 Bezirk6
7 B 07 B 7 Bez7 Bezirk7
8 B 08 B 8 Bez8 Bezirk8
9 B 09 B 9 Bez9 Bezirk9
97 M 51 M 52 MA 51 Sport
96 M 51 M 51 MA 51 Sport
And I want to get the following result:
97 M 51 M 52 MA 51 Sport
96 M 51 M 51 MA 51 Sport
because they have different ID, but they have similar Code OR SImilar Short_text OR simmlar long_text.
Here is what I have tried:
select
ID,
CODE,
DST,
Short_text,
Long_text,
from Main_table tbl
where load_date = (select max(load_date) from Main_table)
and exists
(
select 1 from Main_table
where
tbl.ID != ID
and (tbl.CODE = CODE
or tbl.DST = DST
or tbl.short_text = short_text
or tbl.long_text = long_text)
);
But it doesn't give me a desired result. Do you have ideas how can I improve my query?
That would be
SQL> select * from main_table;
ID CODE DST SHORT LONG_TE
---------- ---- ---- ----- -------
1 B 01 B 1 Bez1 Bezirk1
1 B 01 B 1 Bez1 Bezirk1
2 B 02 B 2 Bez2 Bezirk2
3 B 03 B 3 Bez3 Bezirk3
4 B 04 B 4 Bez4 Bezirk4
4 B 04 B 4 Bez4 Bezirk4
5 B 05 B 5 Bez5 Bezirk5
6 B 06 B 6 Bez6 Bezirk6
7 B 07 B 7 Bez7 Bezirk7
8 B 08 B 8 Bez8 Bezirk8
9 B 09 B 9 Bez9 Bezirk9
97 M 51 M 52 MA 51 Sport
96 M 51 M 51 MA 51 Sport
13 rows selected.
SQL> select a.*
2 from main_table a
3 join main_table b
4 on a.id <> b.id
5 and ( a.code = b.code
6 or a.dst = b.dst
7 or a.short_text = b.short_text
8 or a.long_text = b.long_text);
ID CODE DST SHORT LONG_TE
---------- ---- ---- ----- -------
97 M 51 M 52 MA 51 Sport
96 M 51 M 51 MA 51 Sport
SQL>