help with sql query

Question

hi

i have 2 tables:

Mal: IdNum,Name

Trap: IdNum,Tdate,Name

input: from Tdate to Tdate and Name

i need all the IdNum that in Mal but not in Trap and the input condition

i work on oracle 10g

thank's in advance

Answer 1

select IdNum from Mal where IdNum not in 
(
   select IdNum from Trap
   where Tdate >= to_date(<dateFrom>, 'yyyymmdd')
   and Tdate <= to_date(<dateTo>, 'yyyymmdd')
   and Name = 'name'
)

...where

= 'name'

could also be

'like', 'contains('

or any other flavour of text comparison.

Answer 2

select *
  from mal m
 where not exists (select 1
                     from trap t
                    where m.idnum = t.idnum
                      and t.tdate between <date1> and <date2>
                      and t.name = <name>
                  );