find unjoined records

[michaellunsford]

There are two tables. One table has the generic description and such, the other has a record number from the previous table, and a date relating to that record. Now, there are some records in the first table that do not have any matching records in the second -- and those are the ones I want to isolate: how do I do that?

 

For example:

1st table:
event:
  keyfield => 1
  name => Event Name1
  description => Some descriptive text, usually in html
event:
  keyfield => 2
  name => Event Name2
  description => Some descriptive text
event:
  keyfield => 3
  name => Event Name3
  description => Some descriptive text

2nd table:
event_date:
  keyfield => 0
  event => 1 (keyfield from `event` table)
  date => 2011-10-24
event_date:
  keyfield => 1
  event => 3 (keyfield from `event` table)
  date => 2011-10-24

 

I want to know that the event with the keyfield of '2' does not have a record in the `event_table` I can get it done one record at a time with a php loop -- but I'm thinking MySQL probably has a single routine that can accomplish this. Any idea?

[sunfighter]

I am no expert on joins, but I'm learning. See if this works and please let me know.

SELECT * FROM Table1
LEFT OUTER JOIN Table2
ON Table1.keyfield = Table2.event
WHERE Table2.keyfield IS null

 

 

[michaellunsford]

Now that's just slick!