ID should not display between the specified date?

Question

Using SQL Server 2000

Database1.Table1

ID  Name Date       title    
001 Ravi 23-02-2009 Accountant
001 Ravi 24-02-2009 Accountant
001 Ravi 25-02-2009 Accountant
002 Ram  21-02-2009 Supervisors
002 Ram  25-02-2009 Supervisors
002 Ram  26-02-2009 Supervisors

So on…,

Database2.Table2

ID  Name Date1      Date2    
001 Ravi 23-02-2009 24-02-2009 
002 Ram  21-02-2009 24-02-2009

So on…

Suppose I want to display the records from 21-02-2009 to 26-02-2009 - Expected Output:

001 Ravi 25-02-2009 Accountant
002 Ram 25-02-2009 Supervisors
002 Ram 26-02-2009 Supervisors

From the above two table I want to display id, name, date, title from Database1.table1 where Database1.table1.id should not display between date1 and date2 from Database2.table2:

(23-02-2009 to 24-02-2009) ID should not display from table1
(21-02-2009 24-02-2009) ID should not display from table1

How to make a query in SQL?

Can any one help me.

Answer 1

i think you're looking for this:

select db1.id, db1.name, db1.date, db1.title
from database1.table1 as db1
    join database2.table2 as db2 on db1.id = db2.id
where db1.date < db2.date1
    or db1.date > db2.date2

-don

Answer 2

Try:

SELECT t.id, 
       t.name, 
       t.date, 
       t.title
  FROM TABLE_1 t
 WHERE NOT EXIST(SELECT NULL
                   FROM TABLE_2 t2
                  WHERE t.date BETWEEN t2.date1 AND t2.date2)

If your data model stores dates at strings, update the NOT EXISTS to use CAST/CONVERT to change them to DateTime...

Mind that LEFT JOIN/IS NULL is not as efficient as NOT IN/NOT EXISTS in SQL Server.