Warm tip: This article is reproduced from stackoverflow.com, please click
oracle sql

Selecting rows with exist operator using OR conditions

发布于 2020-04-03 23:39:38

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?

Questioner
LiverToll92
Viewed
71
Littlefoot 2020-01-31 20:21

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>