How to hide the date and id from the table

Using SQL Server 2005

I want to hide the date from table1 where table1.id = table2.id and table1.date = table2.date

Table1

ID Date

001 01-01-2009
001 02-02-2009
001 03-01-2009
001 04-01-2009
002 01-01-2009
002 02-02-2009
002 03-01-2009

...,

Table2

ID Date Holiday

001 01-02-2009 0
001 02-02-2009 1
001 03-01-2009 0
001 04-01-2009 0
002 0开发者_运维技巧1-01-2009 1
002 02-02-2009 1
002 03-01-2009 0

..,

Query

SELECT ID, Date  
FROM table 
WHERE date NOT IN (SELECT date FROM table2 WHERE holiday = 1) 
  AND id NOT IN (SELECT id FROM table2 WHERE holiday = 1)

The above query is displaying nothing.

How to make a query for this condition?

Need SQL Query Help

You can do something like this:

SELECT ID,Date From Table1
WHERE convert(VARCHAR,date)+'~'+convert(VARCHAR,id) NOT IN 
    (SELECT convert(VARCHAR,date)+'~'+convert(VARCHAR,id) FROM table2
     WHERE Holiday=1)

Your id's aren't very unique, and are essentially hiding everything.

For example...

Table 1

001 03-01-2009 - this is not a holiday...

Table 2

001 02-02-2009 1 - this is a holiday - but has an id of "001" - the same ID as the record in table 1 that isn't a holiday!

Are you sure you want to filter based on ID - surely just on date will work:

SELECT
    ID,
    Date
FROM
    table 
WHERE
 date not in (select date from table2 where holiday = 1)