How can I join two tables but only return rows that don't match?

Question

I have two tables which look like this:

T1:  ID  |  Date  |  Hour  | Interval T2:  ID  |  Date  |  Hour 

I basically need to join these tables when their IDs, dates, and hours match. However, I only want to return the results from table 1 that do not match up with the results in table 2.

I know this seems simple, but where I'm stuck is the fact that there are multiple rows in table 1 that match up with table 2 (there are multiple intervals for any given hour). I need to return all of these intervals so long as they do not fall within the same hour period in table 2.

Example data:

T1:  1  |  1/1/2011  |  1  |  1      1  |  1/1/2011  |  1  |  2      1  |  1/1/2011  |  2  |  1      1  |  1/1/2011  |  2  |  2  T2:  1  |  1/1/2011  |  1 

My expected result set for this would be the last two rows from T1. Can anyone point me on the right track?

Answer 1

SELECT T1.*     FROM T1     WHERE NOT EXISTS(SELECT NULL                          FROM T2                          WHERE T1.ID = T2.ID                               AND T1.Date = T2.Date                              AND T1.Hour = T2.Hour) 

It could also be done with a LEFT JOIN:

SELECT T1.*     FROM T1         LEFT JOIN T2             ON T1.ID = T2.ID                 AND T1.Date = T2.Date                 AND T1.Hour = T2.Hour     WHERE T2.ID IS NULL 

Answer 2

Use a LEFT JOIN and filter out the lines that have non-NULL T2 columns:

SELECT T1.* FROM T1 LEFT JOIN T2 ON T1.ID = T2.ID     AND T1.Date = T2.Date AND T1.Hour = T2.Hour     WHERE T2.ID IS NULL