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)
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
)