Avoid duplicates of a column using Not exists SQL ORACLE

Question

How to avoid duplicates of a column using distinct?

I want to get the passports from the table RELATION that doesn't exist to the table PERSON. This it works using the NOT EXISTS.

But I also want to print the relID (it doesn't matter which from each passport).

these are my tables:

RELATION

relID   passport  date
1       400000V   21/07/2019
2       400000V   31/02/2019
3       400000V   31/07/2011
4       342342X   31/12/2012
5       342342X   11/10/2011
6       823972O   31/07/2019

Table PERSON:

id   passport
1    342342X  
2    3333333T
3    1111111W

This is the result I want.

relID   passport
1       400000V     
6       823972O

Thats my query:

select 
     distinct passport
from RELATION
where not exists(select 1 from PERSON where PERSON.passport= RELATION.passport)

If I add the column relID to the select i got duplicates values from passport.

I only want to get unique PASSPORT with one of their relID .

Gordon Linoff · Accepted Answer · 2019-07-31 19:22:01Z

1

Just use aggregation:

select max(relid), passport
from relation
group by passport;

I don't understand what the person table has to do with the question.

answered Jul 31, 2019 at 19:22

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

proera Over a year ago

Because I only want the get the passports that doesn't appear to the table PERSON. For that I need to use a NOT EXISTS.

Bob Jarvis - Слава Україні · Accepted Answer · 2019-08-01 03:38:12Z

1

Use an OUTER JOIN, checking to see if the PASSPORT field in the joined table is NULL, which indicates that the PASSPORT value from RELATION doesn't exist in PERSON:

SELECT r.PASSPORT, MIN(r.RELID)
  FROM RELATION r
  LEFT OUTER JOIN PERSON p
    ON p.PASSPORT = r.PASSPORT
  WHERE p.PASSPORT IS NULL
  GROUP BY r.PASSPORT
  ORDER BY r.PASSPORT

dbfiddle here

answered Aug 1, 2019 at 3:38

Bob Jarvis - Слава Україні

50.2k10 gold badges81 silver badges119 bronze badges

Comments

Zaynul Abadin Tuhin · Accepted Answer · 2019-07-31 19:23:50Z

0

use min ()

select min(relID),passport from table group by passport

answered Jul 31, 2019 at 19:23

Zaynul Abadin Tuhin

32.1k6 gold badges38 silver badges67 bronze badges

Comments

RamSharma · Accepted Answer · 2019-07-31 19:26:01Z

0

Adding group by clause should help :-

select min(relID), passport from RELATION where not exists(select 1 from PERSON where PERSON.passport= RELATION.passport) group by passport;

answered Jul 31, 2019 at 19:26

RamSharma

938 bronze badges

Comments

forpas · Accepted Answer · 2019-07-31 19:46:05Z

0

You have to use NOT EXISTS twice, for the table PERSON and the table RELATION:

select r.relid, r.passport
from RELATION r
where 
  not exists (
    select 1 from PERSON 
    where passport = r.passport
  ) and
  not exists (
    select 1 from RELATION 
    where passport = r.passport and "date" > r."date"
  )

if the date is unique for each passport.
See the demo.
Results:

> RELID | PASSPORT
> ----: | :-------
>     1 | 400000V 
>     6 | 823972O

edited Jul 31, 2019 at 19:46

answered Jul 31, 2019 at 19:26

forpas

165k10 gold badges51 silver badges85 bronze badges

Collectives™ on Stack Overflow

Avoid duplicates of a column using Not exists SQL ORACLE

5 Answers 5

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related