CODEWITHSUNDEEP
sqloracle-database
LiverToll92
LiverToll92

Reputation: 97

Selecting rows with exist operator using OR conditions

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?

Upvotes: 0

Views: 69

Answers (4)

Barbaros Özhan
Barbaros Özhan

Reputation: 65105

You can use count(*) aggregation containing having clauses consecutively :

select ID, Code, DST, Short_text, Long_text
  from Main_table
 where (Code, DST, Short_text, Long_text) in
       (select Code, DST, Short_text, Long_text
          from Main_table
         group by Code, DST, Short_text, Long_text
        having count(*) > 1)
 group by ID, Code, DST, Short_text, Long_text
having count(*) = 1

or count(*) over (partition by...) analytic function to be considered including partition clauses with and without containing ID column :

with m2 as
(
select m.*,
       count(*) over ( partition by Code, DST, Short_text, Long_text ) as cnt1,
       count(*) over ( partition by ID, Code, DST, Short_text, Long_text ) as cnt2
  from Main_table m
)
select ID, Code, DST, Short_text, Long_text
  from m2
 where cnt1 > 1 and cnt2 = 1

Demo

Upvotes: 1

MT0
MT0

Reputation: 167774

You can use analytic functions to avoid a self-join:

Oracle Setup:

CREATE TABLE table_name ( ID, Code, DST, Short_text, Long_text ) as
 select  1,   'B 01',    'B 1',     'Bez1', 'Bezirk1' from dual union all
 select  1,   'B 01',    'B 1',     'Bez1', 'Bezirk1' from dual union all
 select  2,   'B 02',    'B 2',     'Bez2', 'Bezirk2' from dual union all 
 select  3,   'B 03',    'B 3',     'Bez3', 'Bezirk3' from dual union all
 select  4,   'B 04',    'B 4',     'Bez4', 'Bezirk4' from dual union all
 select  4,   'B 04',    'B 4',     'Bez4', 'Bezirk4' from dual union all
 select  5,   'B 05',    'B 5',     'Bez5', 'Bezirk5' from dual union all 
 select  6,   'B 06',    'B 6',     'Bez6', 'Bezirk6' from dual union all
 select  7,   'B 07',    'B 7',     'Bez7', 'Bezirk7' from dual union all
 select  8,   'B 08',    'B 8',     'Bez8', 'Bezirk8' from dual union all 
 select  9,   'B 09',    'B 9',     'Bez9', 'Bezirk9' from dual union all
 select 97,   'M 51',    'M 52',     'MA 51', 'Sport' from dual union all
 select 96,   'M 51',    'M 52',     'MA 51', 'Sport' from dual;

Query:

SELECT ID, Code, DST, Short_text, Long_text
FROM   (
  SELECT t.*,
         COUNT( DISTINCT id ) OVER ( PARTITION BY code       ) AS num_code,
         COUNT( DISTINCT id ) OVER ( PARTITION BY dst        ) AS num_dst,
         COUNT( DISTINCT id ) OVER ( PARTITION BY short_text ) AS num_short_text,
         COUNT( DISTINCT id ) OVER ( PARTITION BY long_text  ) AS num_long_text
  FROM   table_name t
)
WHERE  num_code > 1
OR     num_dst  > 1
OR     num_short_text > 1
OR     num_long_text > 1

Output:

ID | CODE | DST  | SHORT_TEXT | LONG_TEXT
-: | :--- | :--- | :--------- | :--------
96 | M 51 | M 52 | MA 51      | Sport    
97 | M 51 | M 52 | MA 51      | Sport

db<>fiddle here

Upvotes: 1

Dapinder Singh
Dapinder Singh

Reputation: 1

You can use below query

select mt1.ID, mt1.Code, mt1.DST, mt1.Short_text, mt1.Long_text from main_table as mt1 
Cross Apply(
select * from main_table as mt2 where mt1.id!= mt2.id and ( mt1.code=mt2.code or mt1.short_text =mt2.short_text or mt1.long_text = mt2.long_text )
) cv

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 142720

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>

Upvotes: 1

Related Questions