sql data where not exists in a table and not duplicate

Question

its a bit tricky please focus on my requirments, I have 2 tables , I want to get data from first table where not exists in the second AND the data in first column are not duplicate for sub id and child id.

example: I have this table

tab1 

id   subid     childid
1     11       77
2     22       55
3     33       66
4     11       77
7     22       55
8     33       60
9     99       98
10    33       60
11    97       98

tab2

id
1
4
7
10

the first thing I want is the id in tab1 doesnt exists in tab2 which will be 2,3,8,9,11 however some of those id have duplicate subid and chilid so i have to exclude them therefore I should have id 3, 9, 11

I tried this query but it returne me also 3 ,9 ,11, 8 , I dont want 8 how to fix the query ?

select *
  from tab1 a
 where not exists (select 1 from tab2 b where a.id = b.id)
 and a.sub_id in (select c.sub_id
                                from tab1 c
                                group by c.sub_id,c.evt_id
                                having count(1) = 1)

Please tag your RDBMS {sql-server, oracle, access, sqllite, etc) — Igor, Oct 09 '18 at 13:09
@Igor I have to use this query for several rdbms ( oracle , sql server and sybase) — Moudiz, Oct 09 '18 at 13:09

Zaynul Abadin Tuhin · Answer 1 · 2018-10-09T13:18:46.653

1

i think below query will work

select a.*
  from tab1 a
 where not exists (select 1 from tab2 b where a.id = b.id)
 and  not exists (select 1  from tab1 c 
                                 where c.sub_id = a.sub_id 
                                 and c.childid = a.childid
                                 group by c.sub_id,childid
                                 having count(*)> = 2
                              )

edited Oct 09 '18 at 13:18

answered Oct 09 '18 at 13:10

Zaynul Abadin Tuhin

31,407
5
33
63

score 1 · Accepted Answer · answered Oct 09 '18 at 13:13

For multiple database vendors I think the easiest solution would be a couple of not exists queries:

select *
from tab1 a
where not exists (
    select 1 
    from tab2 b 
    where a.id = b.id
)
and not exists (
    select 1 
    from tab1 c 
    where c.sub_id = a.sub_id 
    and c.evt_id = a.evt_id 
    and c.id <> a.id
)

score 1 · Answer 3 · answered Oct 09 '18 at 13:22

Just to add an approach using a CTE, you can first determine the unique childid,subid pairs and then join that table with your main table:

DB Fiddle

Schema (PostgreSQL v9.6)

create table tab1 (
  id integer primary key unique not null
, subid integer not null
, childid integer not null
  );
insert into tab1 (id,subid,childid) values (1, 11, 77);
insert into tab1 (id,subid,childid) values (2, 22, 55);
insert into tab1 (id,subid,childid) values (3, 33, 66);
insert into tab1 (id,subid,childid) values (4, 11, 77);
insert into tab1 (id,subid,childid) values (7, 22, 55);
insert into tab1 (id,subid,childid) values (8, 33, 60);
insert into tab1 (id,subid,childid) values (9, 99, 98);
insert into tab1 (id,subid,childid) values (10, 33,60);
insert into tab1 (id,subid,childid) values (11,    97       ,98);

create table tab2 (
      id integer primary key unique not null
  );

insert into tab2 (id) values (1);
insert into tab2 (id) values (4);
insert into tab2 (id) values (7);
insert into tab2 (id) values (10);

Query #1

with tab1_unique as (
    select subid, childid, count(*) as duplicate
      from tab1
     group by subid, childid
    having count(*) = 1
)
select *
  from tab1 a
  join tab1_unique u on a.subid = u.subid and a.childid = u.childid
 where not exists (select 1 from tab2 b where a.id = b.id);

| id  | subid | childid | subid | childid | duplicate |
| --- | ----- | ------- | ----- | ------- | --------- |
| 11  | 97    | 98      | 97    | 98      | 1         |
| 9   | 99    | 98      | 99    | 98      | 1         |
| 3   | 33    | 66      | 33    | 66      | 1         |

score 0 · Answer 4 · answered Oct 09 '18 at 13:11

not exists should do this:

select t1.*
from (select t1.*, count(*) over (partition by subid, childid) as cnt
      from tab1 t1
     ) t1
where not exists (select 1 from tab2 t2 where t2.id = t1.id) and
      cnt = 1;

You can use not exists as well for the subid/childid with the assumption that the rows are unique in the first table. Without this assumption, window functions are best solution -- and possibly the best solution anyway.

sql data where not exists in a table and not duplicate

4 Answers4