select timestamp that doesn't exist in other table

[J0VE]

Some of you probably know this trick to select rows from one table with an ID value that doesn't exist in another table:

mysql> SELECT table1.* FROM table1
-> LEFT JOIN table2 ON table1.id=table2.id
-> WHERE table2.id IS NULL;

But now take look at this particular situation:

CREATE TABLE `table1` (
`id` int(11) NOT NULL auto_increment,
`data` varchar(255) NOT NULL default '',
`created` timestamp(14),
PRIMARY KEY (`id`)
);

Same goes for table 2.
Now this is my problem: how can i select rows from table 1 with a timestamp `created` that doesn't exist in table 2? the IS NULL trick doesn't seem to work. I guess that is because a timestamp can never be null.

Thanx in advance!

 

[BNPMike]

You need to join on id and created. I bet you're just joining on id.

 

[sleipnir214]

I don't think that's it.

I created two tables "foo" and "bar" based on your CREATE TABLE query.

The data in foo is:

+----+-------+----------------+
| id | data  | created        |
+----+-------+----------------+
|  1 | one   | 20040101000000 |
|  2 | two   | 20040202000000 |
|  3 | three | 20040303000000 |
|  4 | four  | 20040404000000 |
+----+-------+----------------+

The data in bar is:

+----+-------+----------------+
| id | data  | created        |
+----+-------+----------------+
|  1 | one   | 20040101000000 |
|  2 | two   | 20040202010101 |
|  3 | three | 20040303000000 |
|  4 | four  | 20040404000000 |
+----+-------+----------------+

When I perform the query:

select
   *
from
      foo
   left join
      bar
   on
      foo.created = bar.created
where
   bar.created is null

I get the return:

----+------+----------------+------+------+---------+
| id | data | created        | id   | data | created |
+----+------+----------------+------+------+---------+
|  2 | two  | 20040202000000 | NULL | NULL |    NULL |
+----+------+----------------+------+------+---------+

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[sleipnir214]

BNPMike:
My comments weren't directed to your response. You posted before I did.

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[J0VE]

darn...

i'm going to try and find why my resultset contains all records from table1....

 

[sleipnir214]

Jove:
As BNPMike explicitly stated in his post and I implied in my post, in order to get nulls in the "created" column, you have to perform the LEFT JOIN on the "created" column.

Want the best answers? Ask the best questions!

TANSTAAFL!!